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

Date and Time Function in SQL


Database Database 
Ratings:
0 Comment(s)
 3449  View(s)
Rate this:

Date and Time Function in SQL

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

SELECT GETDATE() AS GETDATE, GETUTCDATE() AS GETUTCDATE

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

SELECT DATEPART(month, GETDATE()) AS 'Month Number'

Screen Shot

Date and Time Function in SQL

Example2

SELECT DATEPART(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'

 


Don't want to miss updates? Please click the below button!

Follow MindStick