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’