SQLDateTime function example

This article summarizes the usage and data conversion of DateTime functions and a simple reference manual.

This article summarizes the usage and data conversion of DateTime functions and a simple reference manual.

DateTime-related functions are often used in the T-SQL, and common functions are summarized to be forgotten.

We often use various date formats for some purpose. Of course we can use string operations to construct various date formats, but why are there any ready-made functions?

The default datetime format of the Chinese version of SQL Server is yyyy-mm-dd Thh: mm: ss. mmm.

For example:

Select getdate ()

11:06:08. 177

I sorted out the date format conversion methods that may be frequently used in SQL Server:

SQL DAY ()-MONTH ()-YEAR ()

DAY ('2014-09-30 ') = 30

MONTH ('2014-09-30 ') = 9

YEAR ('1970-09-30 ') = 2008

SQL DATEPART ()

DATEPART (day, '2017-09-30 11:35:00. 100') = 30

DATEPART (month, '2017-09-30 11:35:00. 100') = 9

DATEPART (year, '2017-09-30 11:35:00. 100') = 2008

DATEPART (hour, '2017-09-30 11:35:00. 100') = 11

DATEPART (minute, '2017-09-30 11:35:00. 100') = 35

DATEPART (second, '2017-09-30 11:35:00. 100') = 0

DATEPART (quarter, '2017-09-30 11:35:00. 100') = 3

DATEPART (dayofyear, '2017-09-30 11:35:00. 100') = 2008

DATEPART (week, '2017-09-30 11:35:00. 100') = 40

DATEPART (weekday, '2017-09-30 11:35:00. 100') = 7

DATEPART (millisecond, '2017-09-30 11:35:00. 100') = 2008

DATEPART (microsecond, '2017-09-30 11:35:00. 100') = 2008

DATEPART (nanosecond, '2017-09-30 11:35:00. 100') = 2008

DATEPART (tzoffset, '2017-09-30 11:35:00. 2008-') =-1234567

SQL DATEADD ()

DATEADD (day, 1, '2017-09-30 11:35:00 ') = 2008 01:35:00. 000

DATEADD (month, 1, '2017-09-30 11:35:00 ') = 2008 11:35:00. 000

DATEADD (year, 1, '2017-09-30 11:35:00 ') = 2008 11:35:00. 000

DATEADD (hour, 1, '2017-09-30 11:35:00 ') = 2008 12:35:00. 000

DATEADD (minute, 1, '2017-09-30 11:35:00 ') = 2008 11:36:00. 000

DATEADD (second, 1, '2017-09-30 11:35:00 ') = 2008 11:35:01. 000

DATEADD (quarter, 1, '2017-09-30 11:35:00 ') = 2008 11:35:00. 000

DATEADD (week, 1, '2017-09-30 11:35:00 ') = 2008 11:35:00. 000

DATEADD (month,-1, '2017-09-30 11:35:00 ') = 2008 11:35:00. 000

DATEADD (year, 1.5, '2017-09-30 11:35:00 ') = 2008 11:35:00. 000

SQL DATENAME ()

DATENAME (day, '2017-09-30 11:35:00. 100') = 30

DATENAME (month, '2017-09-30 11:35:00. 100') = September

DATENAME (year, '2017-09-30 11:35:00. 100') = 2008

DATENAME (hour, '2017-09-30 11:35:00. 100') = 11

DATENAME (minute, '2017-09-30 11:35:00. 123') = 35

DATENAME (second, '2017-09-30 11:35:00. 100') = 0

DATENAME (quarter, '2017-09-30 11:35:00. 100') = 3

DATENAME (dayofyear, '2017-09-30 11:35:00. 100') = 2008

DATENAME (week, '2017-09-30 11:35:00. 100') = 40

DATENAME (weekday, '2017-09-30 11:35:00. 100') = Saturday

DATENAME (millisecond, '2017-09-30 11:35:00. 100') = 2008

DATENAME (microsecond, '2017-09-30 11:35:00. 100') = 2008

DATENAME (nanosecond, '2017-09-30 11:35:00. 100') = 2008

DATENAME (tzoffset, '2017-09-30 11:35:00. 2008-') =-

SQL DATEDIFF ()

DATEDIFF (day, '2017-12-01 ', '2017-09-30') = 2007

DATEDIFF (month, '2017-12-01 ', '2017-09-30') = 9

DATEDIFF (year, '2017-12-01 ', '2017-09-30') = 1

DATEDIFF (hour, '06: 46: 45', '11: 35: 00') = 5

DATEDIFF (minute, '06: 46: 45', '11: 35: 00') = 289

DATEDIFF (second, '06: 46: 45', '11: 35: 00') = 17295

DATEDIFF (quarter, '2017-12-01 ', '2017-09-30') = 3

DATEDIFF (week, '2017-12-01 ', '2017-09-30') = 44

DATEDIFF (hour, '2017-09-30 ', '2017-12-01') =-2008

Some other SQL DATE/TIME RELATED FUNCTIONS

GETDATE ()

GETUTCDATE ()

SYSDATETIME ()

SYSUTCDATETIME ()

SYSUTCDATETIMEOFFSET ()

DATEADD (datepart, NUMBER, date)

DATEADIFF (datepart, startdate, enddate)

TODATETIMEOFFSET (datetime2, tzoffset)

SWITCHOFFSET (datetimeoffset, tzoffest)

ISDATE (expression)

Adding to this-Calculate no of Days between two dates excluding Weekends.

DECLARE @ StartDate DATETIME
DECLARE @ EndDate DATETIME
SET @ StartDate = '2014/1/01'
SET @ EndDate = '2014/1/11'

SELECT (DATEDIFF (dd, @ StartDate, @ EndDate) + 1)-(DATEDIFF (wk, @ StartDate, @ EndDate) * 2)
-(Case when datename (dw, @ StartDate) = 'sunday' THEN 1 ELSE 0 END)-(case when datename (dw, @ EndDate) = 'saturday' THEN 1 ELSE 0 END) -- 7

Without century (yy)	With century (yyyy)	Standard	Input/Output **
-	0 or 100 (*)	Default	Mon dd yyyy hh: miAM (or PM)
1	101	USA	Mm/dd/yy
2	102	ANSI	Yy. mm. dd
3	103	British/French	Dd/mm/yy
4	104	German	Dd. mm. yy
5	105	Italian	Dd-mm-yy
6	106	-	Dd mon yy
7	107	-	Mon dd, yy
8	108	-	Hh: mm: ss
-	9 or 109 (*)	Default + milliseconds	Mon dd yyyy hh: mi: ss: mmmAM (or PM)
10	110	USA	Mm-dd-yy
11	111	JAPAN	Yy/mm/dd
12	112	ISO	Yymmdd
-	13 or 113 (*)	Europe default + milliseconds	Dd mon yyyy hh: mm: ss: mmm (24 h)
14	114	-	Hh: mi: ss: mmm (24 h)
-	20 or 120 (*)	ODBC canonical	Yyyy-mm-dd hh: mi: ss (24 h)
-	21 or 121 (*)	ODBC canonical (with milliseconds)	Yyyy-mm-dd hh: mi: ss. mmm (24 h)
-	126 (***)	ISO8601	Yyyy-mm-dd Thh: mm: ss: mmm (no spaces)
-	130 *	Kuwaiti	Dd mon yyyy hh: mi: ss: mmmAM
-	131 *	Kuwaiti	Dd/mm/yy hh: mi: ss: mmmAM

Example:

Select CONVERT (varchar, getdate (), 120)
11:06:08

Select replace (CONVERT (varchar, getdate (), 120 ),'-',''),'',''),':','')
20040912110608

Select CONVERT (varchar (12), getdate (), 111)
2004/09/12

Select CONVERT (varchar (12), getdate (), 112)
20040912

Select CONVERT (varchar (12), getdate (), 102)
2004.09.12

Select CONVERT (varchar (12), getdate (), 101)
09/12/2004

Select CONVERT (varchar (12), getdate (), 103)
12/09/2004

Select CONVERT (varchar (12), getdate (), 104)
12.09.2004

Select CONVERT (varchar (12), getdate (), 105)
12-09-2004

Select CONVERT (varchar (12), getdate (), 106)
12 09 2004

Select CONVERT (varchar (12), getdate (), 107)
09 12,200 4

Select CONVERT (varchar (12), getdate (), 108)
11:06:08

Select CONVERT (varchar (12), getdate (), 109)
09 12, 2004 1

Select CONVERT (varchar (12), getdate (), 110)
09-12-2004

Select CONVERT (varchar (12), getdate (), 113)
12 09 2004 1

Select CONVERT (varchar (12), getdate (), 114)
11:06:08. 177