Date Function in SQL

Date Function in SQL

The various functions are as follows:

Getdate ()

It returns the date and time of the system where the server is running.

Example

select Getdate () Ans: 2016-04-10 15:30:57.073

Day (d)

It returns the day part of the specified date 'd'

Example

(1) select Day (Getdate ()) Ans: 10
(2) select Day ('24/14/89') Ans: Error (format must be MM/DD/YY).

Month (d)

It returns the month part of the given date 'd'.

Example

select Month ('04/10/80')  Ans 4

Example

Queston Write a query to get the list of employees joined in the month of April?
Ans select * from Emp where Month (Hiredate) = 04

Year (d)

It returns the year part of the given date 'd'.

Example

select Year ('12/24/1989')   Ans: 1989

Datename (Datepart, Date)

It is a generalized function which can pick any specific date part value like day, month or year, etc. 

Example

select Datename (DD, Getdate ());

select Datename (mm, Getdate ());

select Datename (yy, Getdate ());

The ‘Datepart’ values can be any of the following prescribed values.

year	yy, yyyy
quarter	qq, q
month	mm, m
day of year	dy, y
day	dd, d
week	sk, ww
weekday	dw
hour	HH, hh
minute	mi, n
seconds	ss, s
milliseconds	ms

Example

Question Write a program to get the details of employee who has joined on 'Monday'?
Answer select * from Emp where Datename (dw, Hiredate) = 'Monday'

DateAdd (datepart, n, d)

 It returns a new date value by adding the specified no. of datepart values to the given date.

Example

select DateAdd (DD, 75, Getdate ()) Ans 2016-06-24 15:52:54.417

DateDiff (Datepart, D1, D2)

It returns the difference between D1 and D2 in the specified 'Datepart' format.

Example

Question Write a query to find out the no. of years each employee is working in the organization?

Answer  

select * from Emp where DateDiff (yy, Hiredate, Getdate ())  (wrong)

select DateDiff (yy, Hiredate, Getdate ()) from Emp  (right)

Question Write a query to find how many years now completed from Independence day?

Answer select DateDiff (yy, '08-15-1947', Getdate ())

Question Write a query to get a list of employees who are working for more than 28 years?

Answer select * from Emp where DateDiff (yy, Hiredate, Getdate ())