Sql server date function

As in article Sql Server dateparts values and abbreviations we have learnt about dateparts in sql server. Now In this  article we will learn manipulation of datetime values with date functions. Date functions can be used in then column_list, the Where clause or wherever an expression can be used


Date functions
Function Results
DATEADD(datepart,number,date) Adds the number of dateparts to the date
DATEDIFF(datepart,date1,date2) Number of dateparts between two dates
 DATENAME(datepart,date)  Returns ASCII value for specified datepart for date
 DATEPART(datepart,date)  Returns integer value for specified datapart for date
 GETDATE() Returns Current date and time in internal format



Date function Examples

Function Results
SELECT DATEADD(mm,-2,'01/03/2010') 2010-01-01 00:00:00.000
SELECT DATEDIFF(mm,'01/02/2009','12/31/2010') 23
SELECT GETDATE() 12/29/2010
SELECT DATEADD(mm,6,'01/01/2010') 2010-07-01 00:00:00.000
SELECT DATENAME(mm,'01/01/2010') January