articles

Home / DeveloperSection / Articles / Date and Time Function in SQL

Date and Time Function in SQL

AVADHESH PATEL6113 25-Sep-2012

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

Date and Time Function in SQL

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

Date and Time Function in SQL

Example2

SELECTDATEPART(m, 0) AS MONTH, DATEPART(d, 0) AS DATE, DATEPART(yy, 0) AS YEAR

Date and Time Function in SQL

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

Date and Time Function in SQL

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

Date and Time Function in SQL

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

Date and Time Function in SQL

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

Date and Time Function in SQL

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' 

Updated 07-Sep-2019
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By