Saturday, December 24, 2011

Date functions in Sql Server

Date functions are the scalar functions that operate date type of data.Here i will show how to perform the all the operation using data function in sql server
Get the current date:
The GETDATE() returns the system date and time on server
Select GETDATE()
Get specified path from date:
The datepart(item,Date) is used to extract specified path from the date
select Datepart(day,GETDATE())
//get the day,quarter and week of the year
select Datepart(dw,GETDATE())returns day of the week
select Datepart(qq,GETDATE())returns quarter of the year
select Datepart(ww,GETDATE())returns week of the year for give date
select Datepart(hour,GETDATE())
select Datepart(month,GETDATE())returns day of the week
//*Here i will show an example to get the employee details who are joining in December 2001
select*from emp where Datepart(month,hireddate)=12 and Datepart(year,hireddate)=2001
Get the Difference between dates:
The Datediff(item,date1,date2) function is used to get the difference between give dates in the specified Item
select Datediff(day,'12/24/2011',GETDATE())
select Datediff(month,'12/24/2011',GETDATE())
select Datediff(year,'12/24/2011',GETDATE())
select Datediff(ww,'12/24/2011',GETDATE())
select Datediff(qq,'12/24/2011',GETDATE())
//*Here i will show an example to get the employee details who are having 10 years of experience in the company
select*from emp where Datediff(year,hireddate,GETDATE())>10
Add date:
DateAdd(item,n,date) function returns a date after adding the specified no of items to the given date
select DateAdd(day,'5',GETDATE())-->29/12/2011
select DateAdd(day,'-5',GETDATE())-->19/12/2011
select DateAdd(month,'5',GETDATE())-->29/5/2011

No comments: