Date and time functions allow manipulating columns and variables with DATETIME data types.
List of Date and Time Function
1. GETDATE and GETUTCDATE Functions
2. DATEPART Function
3. DATENAME Function
4. DAY, MONTH, and YEAR Functions
5. DATEADD Functions
6. DATEDIFF Function
GETDATE and GETUTCDATE Functions
GETDATE and GETUTCDATE functions both return the current date and time. However, GETUTCDATE returns the current Universal Time Coordinate (UTC) time, whereas GETDATE returns the date and time on the computer where SQL Server is running. The GETUTCDATE() function compares the time zone of SQL Server computer with the UTC time zone. Neither of these functions accepts parameters, and they are both non-deterministic.
Syntax
GETDATE()
GETUTCDATE()
Example
SELECTGETDATE()ASGETDATE,GETUTCDATE()ASGETUTCDATE
Screen Shot
DATEPART Function
The DATEPART function allows retrieving any part of the date and time variable provided. This function is deterministic except when used with days of the week.
The DATEPART function takes two parameters: the part of the date that you want to retrieve and the date itself. The DATEPART function returns an integer representing any of the following parts of the supplied date: year, quarter, month, day of the year, day, week number, weekday number, hour, minute, second, or millisecond.
Syntax
DATEPART(datepart, date )
Example1
SELECTDATEPART(month,GETDATE())AS'Month Number'
Screen Shot
Example2
SELECTDATEPART(m, 0) AS MONTH, DATEPART(d, 0) AS DATE, DATEPART(yy, 0) AS YEAR
In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.
DATEPART parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.
Datepart |
Abbreviations |
year |
yy, yyyy |
quarter |
qq, q |
month |
mm, m |
dayofyear |
dy, y |
day |
dd, d |
week |
wk, ww |
weekday |
dw |
hour |
hh |
minute |
mi, n |
second |
ss, s |
millisecond |
ms |
DATENAME Function
The DATENAME nondeterministic function returns the name of the portion of the date and time variable. Just like the DATEPART function, the DATENAME function accepts two parameters: the portion of the date that you want to retrieve and the date. The DATENAME function can be used to retrieve any of the following: name of the year, quarter, month, day of the year, day, week, weekday, hour, minute, second, or millisecond of the specified date.
Syntax
DATENAME ( datepart , date )
Example
SELECT DATENAME(month, getdate()) AS 'Month Name'
Screen Shot
DAY, MONTH, and YEAR Functions
DAY, MONTH and YEAR functions are deterministic. Each of these accepts a single date value as a parameter and returns respective portions of the date as an integer.
Syntax
DAY ( date )
MONTH( date)
YEAR( date )
Example
SELECT DAY(getdate()) AS DAY, MONTH(getdate())AS MONTH, YEAR(getdate())AS YEAR
Screen Shot
DATEADD Functions
DATEADD function is deterministic; it adds a certain period of time to the existing date and time value.
Syntax
DATEADD ( datepart , number, date )
Example
SELECT GETDATE()AS 'TODAY DATE', DATEADD(day, 21, getdate()) AS 'EXCEED DATE'
Screen Shot
DATEDIFF Function
DATEDIFF function is deterministic; it accepts two DATETIME values and a date portion (minute, hour, day, month, etc) as parameters. DATEDIFF() determines the difference between the two date values passed, expressed in the date portion specified. Notice also that start date should come before the end date, if you'd like to see positive numbers in the result set.
Syntax
DATEDIFF ( datepart , startdate , enddate )
Example
SELECT DATEDIFF(day, '2012-07-01', '2012-08-27') 'AS NO OF DAY'
Screen Shot
Some more example of DATETIME function:-
----Today
SELECT GETDATE() 'Today'
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'
Leave Comment