SQL Server time format functions

I. Time Functions

When using stored procedures and SQL functions, you may encounter time processing. For example, get and add or subtract time. The built-in Time Functions of SQL are used here. I will list these functions to facilitate future memory usage.

 --  Getdate: Get the current time   Select   Getdate  ()  --  Dateadd original time plus: 13:20:16 this time plus 12 months   Select   Dateadd ( Month , 12 , ' 2013-02-17 13:20:16  ' ) --  Return Value: 13:20:16. 000 (the month parameter can be changed to day, year, and other dates plus corresponding values)    --  Datediff two time difference (followed by-front = return value)   Select   Datediff ( Day , '  2013-02-01  ' , '  2013-02-18  ' )--  Return Value: 17 (the parameter day can be changed to month, year, and other dates plus corresponding values)    --  Datepart obtains an integer of a certain part of the date.   Select   Datepart ( Month , '    ' ) --  Returns 2 (the month parameter can be changed to day, year, and other dates plus corresponding values)    --  Datename gets the string of the specified part   Select  Datename (Weekday, '    ' ) --  Returns Sunday (the weekday parameter can be changed to day, year, and other dates plus corresponding values)   --  Day (), month (), Year () Get the string of the specified part   Select   Day ( '    ' ) --  Return 15 

Ii. Time Format Conversion

 

     Select   Convert ( Varchar , Getdate (), 120 ) --  Returns 13:37:54      Select   Replace ( Replace ( Replace ( Convert (Varchar , Getdate (), 120 ), '  -  ' , '' ), '   ' , '' ), '  :  ' , '' ) -- Returns 20130217133828      Select   Convert ( Varchar ( 12 ), Getdate (), 111 ) --  Returns 2013/02/17.      Select   Convert ( Varchar ( 12 ),Getdate (), 112 ) --  Returns 20130217      Select   Convert ( Varchar ( 12 ), Getdate (), 102 ) --  Returns 2013.02.17.      Select   Convert (Varchar ( 12 ), Getdate (), 101 ) --  Returns 02/17/2013.      Select   Convert ( Varchar ( 12 ), Getdate (), 103 ) -- Returns 17/02/2013.      Select   Convert ( Varchar ( 12 ), Getdate (), 104 ) --  Returns 172.162.2013.      Select   Convert ( Varchar ( 12 ), Getdate (), 105 ) --  Returns 17-02-2013      Select   Convert ( Varchar ( 12 ), Getdate (), 106 ) --  Returns 17 02 2013      Select   Convert ( Varchar ( 12 ), Getdate (), 107 ) --  Returns 02 17,201 3      Select   Convert ( Varchar ( 12 ), Getdate (), 108 ) --  Returns 13:42:50.     Select   Convert ( Varchar ( 12 ), Getdate (), 109 ) --  Returns 02 17 2013      Select   Convert ( Varchar ( 12 ), Getdate (),110 ) --  Return 02-17-2013      Select   Convert ( Varchar ( 12 ), Getdate (), 113 ) --  Return 17 02 2013 1      Select   Convert ( Varchar (12 ), Getdate (), 114 ) --  Returns 13: 42: 24: 743 

 

Iii. Time Format related tables

 

Function	Parameters/functions
Getdate ()	Returns the current date and time of the system.
Datediff (interval, date1, date2)	Returns the difference date2-date1 between date2 and date1 as specified by interval
Dateadd (interval, number, date)	Add the date after number in the format specified by interval
Datepart (interval, date)	Returns the integer corresponding to the specified part of interval in date.
Datename (interval, date)	Returns the string name corresponding to the specified part of interval in date.

 

Value	SQL Server)	Access and ASP	Description
Year	YY	Yyyy	1753 ~ 9999
Quarter	Qq	Q	Season 1 ~ 4
Month	Mm	M	Month 1 ~ 12
Day of year	Dy	Y	The number of days in a year. The number of days in a year is 1-366.
Day	Dd	D	, 1-31
Weekday	DW	W	The number of days in a week. The number of days in a week is 1-7.
Week	WK	WW	Week, week 0 ~ 51
Hour	HH	H	Time 0 ~ 23
Minute	Mi	N	Minute 0 ~ 59
Second	SS	S	Seconds 0 ~ 59
millisecond	MS	-	millisecond 0 ~ 999