SQL Server - Date Time Functions

Here are a few examples of functions that can help you manipulate DATE in different manners. These will comprise most of your daily job routines. The below set of Date/Time functions are used consistently by SQL Users as they are most oftenly required to retrieve specific data from the database.

---------------------------------------------------------------------------------
Please note that this article is created on 3rd of Febraury 2010 and getDate() function will return current date.
---------------------------------------------------------------------------------

The Following statement will return the date, month and year.
SELECT DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()))
Result : 2010-02-03 00:00:00.000

SELECT LEFT(DATEADD(Day,0, DATEDIFF(Day, 0, GETDATE())), 12)
Result : Feb 3 2010

The following statement will return only date.
SELECT DATEPART(Day, GETDATE())
Result : 3

The Following statement will return the day-number for the week.
SELECT DATEPART(dw, GETDATE())
Result : 4

The Following statement will return day name of the week.
SELECT DATENAME(dw, GETDATE())
Result : Wednesday

The Following statement will return month of the year.
SELECT DATENAME(mm, GETDATE())
Result : February

The Following statements will return the year. You can use 'yy' instead of 'year'
SELECT DATENAME(year, GETDATE())
SELECT DATEPART(yy, GETDATE())
Result : 2010

The Following statements will return the month-number for the year. You can use 'mm' instead of 'month'.
SELECT DATEPART(month, GETDATE())
Result : 2

Difference between two dates (number of days between two dates). You can use 'd' or 'dd' instead of 'Day'
SELECT DATEDIFF(Day, '2010-01-01', GETDATE())
Result : 33

Difference between two dates (number of months between two dates)
SELECT DATEDIFF(Month, '2010-01-01', GETDATE())
Result : 1

Difference between two dates (number of minutes between two dates)
SELECT DATEDIFF(Minute, '2010-01-01', GETDATE())
Result : 48531
SELECT CONVERT(DECIMAL(5,2), DATEDIFF(Second, '2010-02-01 10:55:00.000', '2010-02-01 19:58:10.498')/60.00)
Result : 543.17

Difference between two dates (number of seconds between two dates)
SELECT DATEDIFF(Second, '2010-01-01', GETDATE())
Result : 2911914

Difference between two dates (number of years between two dates)
SELECT DATEDIFF(Year, '2010-01-01', GETDATE())
Result : 0

SELECT Day2-Day1 FROM (
SELECT
Day1=CONVERT(DATETIME, '2010-02-01 05:47:53.497'),
Day2=CONVERT(DATETIME, '2010-02-01 06:47:10.420')) Date
Result : 1900-01-01 00:59:16.923

SELECT
CONVERT(VARCHAR(10),
CASE WHEN(
DATEDIFF(hh, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497')) >= 24)
THEN
DATEDIFF(hh, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497'))-24
ELSE
DATEDIFF(hh, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497'))
END)
+
SUBSTRING(CONVERT(VARCHAR(30),
DATEADD(mi, DATEDIFF(Minute, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497')),
DATEADD(s, DATEDIFF(Second, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497')), 0)), 109), 15, 6) as 'HH:MM:SS'
Result : 22:58:06
-----------------------------------------------------------------
DATE Format using CONVERT Function
-----------------------------------------------------------------
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Result : 'Feb 1 2010 12:11PM'
SELECT CONVERT(VARCHAR(20), GETDATE(), 101)
Result : '02/01/2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 102)
Result : '2010.02.01'
SELECT CONVERT(VARCHAR(20), GETDATE(), 103)
Result : '01/02/2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 104)
Result : '01.02.2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 105)
Result : '01-02-2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 106)
Result : '01 Feb 2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 107)
Result : 'Feb 01, 2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 108)
Result : '12:15:48'
SELECT CONVERT(VARCHAR(40), GETDATE(), 109)
Result : 'Feb 1 2010 12:16:32:570PM'
SELECT CONVERT(VARCHAR(40), GETDATE(), 110)
Result : '02-01-2010'
SELECT CONVERT(VARCHAR(40), GETDATE(), 111)
Result : '2010/02/01'
SELECT CONVERT(VARCHAR(40), GETDATE(), 112)
Result : '20100201'
SELECT CONVERT(VARCHAR(40), GETDATE(), 113)
Result : '01 Feb 2010 12:17:26:710'
SELECT CONVERT(VARCHAR(40), GETDATE(), 114)
Result : '12:17:40:040'
SELECT CONVERT(VARCHAR(40), GETDATE(), 120)
Result : '2010-02-01 12:18:24'
SELECT CONVERT(VARCHAR(40), GETDATE(), 121)
Result : '2010-02-01 12:18:37.007'
SELECT CONVERT(VARCHAR(40), GETDATE(), 126)
Result : '2010-02-01T12:19:07.160'
SELECT CONVERT(VARCHAR(40), GETDATE(), 127)
Result : '2010-02-01T12:19:34.537'
SELECT CONVERT(VARCHAR(40), GETDATE(), 130)
Result : '17 ??? 1431 12:20:37:580PM'
SELECT CONVERT(VARCHAR(40), GETDATE(), 131)
Result : '17/02/1431 12:21:04:597PM'