DATE FUNCTIONS IN SQL SERVER

DATE FUNCTIONS

getdate(): returns current system date & time

Eg: select getdate()

day(): returns day part from the date

Eg: select day(getdate()

select day('10/24/78')

Month(date)

returns month part from the date

Eg: 

select month(getdate())

select month('10/24/78')

Year():

Returns year part of the date.

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Eg:

select year('10/24/78')

select year(getdate())

Display employee records joined in leap year

select * from emp where year(hiredate)%4=0

Display employee records between January and April.

select * from emp where month(hiredate) between 1 and 4

Display employee records who are joined in first 15 days of January month in year 1981

select * from emp where day(hiredate) between 1 and 15 and month(hiredate)=1 and year(hiredate)=1981

DatePart(part,date)

It is used to extract specified part from the given date.

select datepart(dd,getdate())

o/p-7    (Day of the month)

     

select datepart(mm,getdate())

o/p-10    (month of the year)

select datepart(yy,getdate())

o/p-2013   (year of the date)

select datepart(dw,getdate())

o/p -2    (day of the weak)

select datepart(ww,getdate())

o/p-41   (weak of the year)

select datepart(qq,getdate())

o/p-4   (quarter of the year)

select datepart(hh,getdate())

o/p-22   (hours of the date)

select datepart(ss,getdate())

o/p-19   (seconds of the date)

select datepart(mi,getdate())

O/p-52   (minutes of the date)

DateName()

This function is same as date part expect that while extracting month and day of the week it will return month name and weak name instead of Name.

given date.

select datename(dd,getdate())

o/p-7    (Day of the month)

     

select datename (mm,getdate())

o/p-october    (month of the year)

select datename (yy,getdate())

o/p-2013   (year of the date)

select datename (dw,getdate())

o/p -Monday    (day of the weak)

select datename (ww,getdate())

o/p-41   (weak of the year)

select datename (qq,getdate())

o/p-4   (quarter of the year)

select datename (hh,getdate())

o/p-22   (hours of the date)

select datename (ss,getdate())

o/p-19   (seconds of the date)

select datename (mi,getdate())

O/p-52   (minutes of the date)

Display smith joined on Sunday as outpu

select ename + ' joined on ' +datename(dw,hiredate) from emp

   

DateAdd()

It is used to add or subtract n no of specified parts to the given date and return a date.

Add 6 days to the getdate

select DATEADD(dd,6,getdate())

output:2013-10-18 09:52:49.720

Add 6 months to the getdate

select DATEADD(mm,6,getdate())

output: 2014-04-12 09:53:42.340

Subtract 6 days to the getdate.

select DATEADD(dd,-6,getdate())

output: 2013-10-06 09:54:41.340

DateDifference()

It is used to get the difference between the two different dates in specified format.

Select datediff(dd,'03/01/2012',getdate())

Output: 590

Select datediff(mm,'03/01/2012',getdate())

Output: 19

Select datediff(ww,'03/01/2012',getdate())

Output:84

getUTCDate()

Returns the date time value representing the current UTC time(Coordinated universal Time or Greenwich Mean Time).

Select GETUTCDATE()

Find the employees who are joined in the company in Dec Month of every year.

select * from emp where datepart(mm,hiredate)=12

or if you want only 2013 year

than write the query as

select * from emp where datepart(mm,hiredate)=12 and datepart(yy,hiredate)=2013

Find the programmer whose DOB is on system date.

select * from PROGRAMMER where datepart(dd,dob)=datepart(dd,getdate()) and datepart(mm,dob)=datepart(mm,getdate())