SQL Date () function

SQL Server Date function

The following table lists the most important built-in date functions in SQL Server:

function	Description
GETDATE ()	Returns the current date and time
DATEPART ()	Returns a separate part of the date/time
DATEADD ()	Add or subtract a specified time interval in a date
DATEDIFF ()	Returns a time between two dates
CONVERT ()	Display Date/time in a different format

The (1), GETDATE () function returns the current date and time from SQL Server.

GrammarGETDATE () Example

Here is the SELECT statement:

SELECT GETDATE () as Currentdatetime

The results are as follows:

Currentdatetime
2008-11-11 12:45:34.243

(2),

The DATEPART () function is used to return a separate part of a date/time, such as year, month, day, hour, minute, and so on.

GrammarDATEPART (Datepart,date)

The date parameter is a valid day expression. The datepart parameter can be the following value:

DatePart	Abbreviations
Years	YY, yyyy
Quarter	QQ, Q
Month	MM, M
The day of the year	Dy, y
Day	DD, D
Week	WK, WW
Week	DW, W
Hours	hh
Minutes	MI, n
Seconds	SS, S
Milliseconds	Ms
Subtle	MCs
Na-Sec	Ns

Example

Suppose we have the following "Orders" table:

OrderId	ProductName	OrderDate
1	Jarlsberg Cheese	2008-11-11 13:23:44.657

Here is the SELECT statement:

SELECT DATEPART (yyyy,orderdate) as OrderYear, DATEPART (mm,orderdate) as OrderMonth, DATEPART (dd,orderdate) as Orderday, From Orders WHERE orderid=1

The results are as follows:

OrderYear	OrderMonth	Orderday
2008	11	11

(3),

The DATEADD () function adds or subtracts a specified time interval from a date.

GrammarDATEADD (Datepart,number,date)

The date parameter is a valid day expression. The number is the number of intervals you want to add, and for the future time, this is positive, and for the past time, this number is negative.

The datepart parameter can be the following value:

DatePart	Abbreviations
Years	YY, yyyy
Quarter	QQ, Q
Month	MM, M
The day of the year	Dy, y
Day	DD, D
Week	WK, WW
Week	DW, W
Hours	hh
Minutes	MI, n
Seconds	SS, S
Milliseconds	Ms
Subtle	MCs
Na-Sec	Ns

Example

Suppose we have the following "Orders" table:

OrderId	ProductName	OrderDate
1	Jarlsberg Cheese	2008-11-11 13:23:44.657

Now we want to add 45 days to "OrderDate" so we can find the payment date.

We use the following SELECT statement:

SELECT Orderid,dateadd (day,45,orderdate) as orderpaydate from Orders

Results:

OrderId	orderpaydate
1	2008-12-26 13:23:44.657

(4),

The DATEDIFF () function returns the number of days between two dates.

GrammarDATEDIFF (Datepart,startdate,enddate)

The StartDate and EndDate parameters are valid date expressions. The datepart parameter can be the following value:

DatePart	Abbreviations
Years	YY, yyyy
Quarter	QQ, Q
Month	MM, M
The day of the year	Dy, y
Day	DD, D
Week	WK, WW
Week	DW, W
Hours	hh
Minutes	MI, n
Seconds	SS, S
Milliseconds	Ms
Subtle	MCs
Na-Sec	Ns

Example

Now we want to get a number of days between two dates.

We use the following SELECT statement:

SELECT DATEDIFF (Day, ' 2008-06-05 ', ' 2008-08-05 ') as Diffdate

Results:

Diffdate
61

Example

Now we want to get two days between dates (note that the second date is earlier than the first date and the result will be a negative number).

We use the following SELECT statement:

SELECT DATEDIFF (Day, ' 2008-08-05 ', ' 2008-06-05 ') as Diffdate

Results:

Diffdate
-61

(5),

The CONVERT () function is a general function that converts a date to a new data type.

The CONVERT () function can display date/time data in different formats.

Grammar convert ( data_type (length), expression, style)

value	Description
Data_type (length)	Specifies the target data type (with an optional length).
Expression	Specifies the value that needs to be converted.
Style	Specifies the date/time of the output format.

The following table shows the style values that are converted to character data by datetime or smalldatetime:

value (Century yy)	value (century yyyy)	Input/Output	Standard
-	0 or 100	Mon dd yyyy hh:miam (or PM)	Default
1	101	Mm/dd/yy	USA
2	102	Yy.mm.dd	Ansi
3	103	Dd/mm/yy	British/french
4	104	Dd.mm.yy	German
5	105	Dd-mm-yy	Italian
6	106	DD Mon yy
7	107	Mon DD, yy
8	108	Hh:mm:ss
-	9 or 109	Mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default+millisec
10	110	Mm-dd-yy	USA
11	111	Yy/mm/dd	Japan
12	112	Yymmdd	Iso
-	113 or	DD Mon yyyy hh:mi:ss:mmm (24h)
14	114	Hh:mi:ss:mmm (24h)
-	120 or	Yyyy-mm-dd Hh:mi:ss (24h)
-	or 121	Yyyy-mm-dd hh:mi:ss.mmm (24h)
-	126	Yyyy-mm-ddthh:mi:ss.mmm (no spaces)	ISO8601
-	130	DD Mon yyyy hh:mi:ss:mmmAM	Hijiri
-	131	Dd/mm/yy Hh:mi:ss:mmmAM	Hijiri

Example

The following script uses the CONVERT () function to display different formats. We will use the GETDATE () function to get the current date/time:

CONVERT (varchar), GETDATE ()) CONVERT (varchar (), GETDATE (), () convert (varchar), GETDATE (), () Convert ( varchar (one), GETDATE (), 6) CONVERT (varchar (one), GETDATE (), 106) CONVERT (varchar), GETDATE (), 113)

The results are as follows:

Nov 11:45 PM 11-04-11 11-04-2011, Geneva 2011 11:45:34:243

SQL Date () function