Now () Function in SQL Server

Now () Function in SQL Server 2005

Logs, Status Reports and et al., Now() function is common. Here is the way to accomplish the same in SQL Server

Select @Today = GetDate() should give you the same

DatePart - Get Part of the Date

-- Selecting the Current Year/Get Current Year/Separate Year Part from Date
Select DatePart(YY, GetDate()) as Current_Year

-- Selecting the Current Quarter/Get Current Quarter/Separate Quarter Part from Date
Select DatePart(QQ, GetDate()) as Current_Quarter

-- Selecting the Current Month/Get Current Month/Separate Month Part from Date
Select DatePart(MM, GetDate()) as Current_Month

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(month, GetDate()) as Current_Month_Name

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(day, GetDate()) as Current_Day

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(wk, GetDate()) as Current_Week

Posted by Shasur at 5:14 PM 0 comments

Identify Column in All Tables

use AdventureWorks
Select * From sys.columns
select * from sys.tables

-- Select Columns From All Tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id

-- Select Specific Columns and their corresponding tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id
and sc.name = 'EmployeeID'

-- Select Specific Columns and their corresponding tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id
and sc.name = 'PurchaseOrderID'

Maximum Connections for SQL Server

All About Connections

@@MAX_Connections Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured

Select @@MAX_Connections as Max_Connections

No of attempted connections for SQL Server

Select @@Connections as TotalLoginAttempts

This will include either successful or unsuccessful since SQL Server was last started.

You can also check the same using sp_monitor System procedure

Exec sp_monitor

SQL Adding or Subtracting Dates

********************Adding or Subtracting Dates********************

If the requirement is to calculate the age of a person six months from now, dateadd function can help

Select BirthDate, DateDiff(year, BirthDate, Dateadd(month, 6, getdate())) as BirthDateAfterSix from HumanResources.Employee Order By 1 Desc

subtracting dates is equally easy:

Select BirthDate, DateDiff(year, BirthDate, Dateadd(month, -6, getdate())) as BirthDateAfterSix from HumanResources.Employee Order By 1 Desc

GetAge - Get Age Using SQL Query

How to calculate Age using SQL Query

DateDiff function can be used to get the Age from the birth date

Select BirthDate, DateDiff(year, BirthDate, getdate()) as Age from HumanResources.Employee

Tie in Max or Min Function - WITH TIES

If you have two employees with the same birth date - the youngest/oldest employee has a tie. Then use of WITH TIES clause will solve it.

If WITH TIES is also specified, all rows that contain the last value returned by the ORDER BY clause are returned, even if doing this exceeds the number specified by expression.

Top 1 could return two or even more rows in case of a Tie

Select TOP 1 WITH TIES BirthDate from HumanResources.Employee Order By 1 Desc

WITH TIES can be used to get the matching records of the last/first even if it is more than the given number or percent

WITH TIES requires an ORDER BY clause.

Youngest Employee - SQL TOP

********************Youngest & Oldest Employee********************

To get the youngest / olderst employee, two different methods are shown here.

1. Using Max and Min Functions
2. Using Top in Select Statement

'' Youngest Employee (Reference : AdventureWorks)


Select Min(BirthDate) from HumanResources.Employee
Select TOP 1 BirthDate from HumanResources.Employee Order By 1 Asc
Select TOP 1 * from HumanResources.Employee Order By BirthDate Asc

Select * from Person.Contact where ContactID = (Select TOP 1 EmployeeID from HumanResources.Employee Order By BirthDate Asc)

'' Oldest Employee (Reference : AdventureWorks)


Select Max(BirthDate) from HumanResources.Employee
Select TOP 1 BirthDate from HumanResources.Employee Order By 1 Desc










Free Search Engine Submission






AddMe - Search Engine Optimization


Homerweb Search