SQL Server Date-time-to-string

One, SQL Server date-time functions
Date and time functions in SQL Server
1. Current system date, time
Select GETDATE ()

2. DateAdd returns a new datetime value based on adding a period of time to the specified date
Example: Add 2 days to the date
Select DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.000

3. DateDiff returns the number of date and time boundaries across two specified dates.
Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17

4. DatePart returns an integer representing the specified date portion of the specified date.
Select DATEPART (Month, ' 2004-10-15 ')--return 10

5. Datename returns a string representing the specified date part of the specified date
Select Datename (Weekday, ' 2004-10-15 ')--return: Friday

6. Day (), month (), year ()--can be compared with datepart

Select Current date =convert (varchar), GETDATE (), 120)
, Current time =convert (varchar (8), GETDATE (), 114)

Select Datename (DW, ' 2004-10-15 ')

Select how many weeks of the year =datename (week, ' 2004-10-15 ')
, today is a few weeks =datename (weekday, ' 2004-10-15 ')

Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am
Select CONVERT (varchar), GETDATE (), 1): 05/16/06
Select CONVERT (varchar), GETDATE (), 2): 06.05.16
Select CONVERT (varchar), GETDATE (), 3): 16/05/06
Select CONVERT (varchar), GETDATE (), 4): 16.05.06
Select CONVERT (varchar), GETDATE (), 5): 16-05-06
Select CONVERT (varchar), GETDATE (), 6): 16 05 06
Select CONVERT (varchar), GETDATE (), 7): 05 16, 06
Select CONVERT (varchar), GETDATE (), 8): 10:57:46
Select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827am
Select CONVERT (varchar), GETDATE (), 10): 05-16-06
Select CONVERT (varchar), GETDATE (), 11): 06/05/16
Select CONVERT (varchar), GETDATE (), 12): 060516
Select CONVERT (varchar), GETDATE (), 13): 16 05 2006 10:57:46:937
Select CONVERT (varchar), GETDATE (), 14): 10:57:46:967
Select CONVERT (varchar), GETDATE (), 20): 2006-05-16 10:57:47
Select CONVERT (varchar), GETDATE (), 21): 2006-05-16 10:57:47.157
Select CONVERT (varchar), GETDATE (): 05/16/06 10:57:47 AM
Select CONVERT (varchar), GETDATE (), 23): 2006-05-16
Select CONVERT (varchar), GETDATE (), 24): 10:57:47
Select CONVERT (varchar), GETDATE (), 25): 2006-05-16 10:57:47.250
Select CONVERT (varchar), GETDATE (), (+): 2006 10:57am
Select CONVERT (varchar), GETDATE (), 101): 05/16/2006
Select CONVERT (varchar), GETDATE (), 102): 2006.05.16
Select CONVERT (varchar), GETDATE (), 103): 16/05/2006
Select CONVERT (varchar), GETDATE (), 104): 16.05.2006
Select CONVERT (varchar), GETDATE (), 105): 16-05-2006
Select CONVERT (varchar), GETDATE (), 106): 16 05 2006
Select CONVERT (varchar), GETDATE (), 107): 05 16, 2006
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
Select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437am
Select CONVERT (varchar), GETDATE (), (+): 05-16-2006
Select CONVERT (varchar), GETDATE (), 111): 2006/05/16
Select CONVERT (varchar), GETDATE (), 20060516 ):
Select CONVERT (varchar), GETDATE (), 113): 2006 10:57:49:513
Select CONVERT (varchar), GETDATE (), (): 10:57:49:547
Select CONVERT (varchar), GETDATE (), (+): 2006-05-16 10:57:49
Select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700
Select CONVERT (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827
Select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907am

Function	Parameters/Functions
GetDate ()	Returns the current date and time of the system
DateDiff (INTERVAL,DATE1,DATE2)	Returns the difference between Date2 and date1 two dates, as specified by interval date2-date1
DATEADD (Interval,number,date)	The date specified by interval, plus number
DatePart (Interval,date)	Returns the integer value that corresponds to the specified part of the date interval
Datename (Interval,date)	Returns the string name corresponding to the specified part of the date, interval

The setting values for the parameter interval are as follows:

Function	Parameters/Functions
GetDate ()	Returns the current date and time of the system
DateDiff (INTERVAL,DATE1,DATE2)	Returns the difference between Date2 and date1 two dates, as specified by interval date2-date1
DATEADD (Interval,number,date)	The date specified by interval, plus number
DatePart (Interval,date)	Returns the integer value that corresponds to the specified part of the date interval
Datename (Interval,date)	Returns the string name corresponding to the specified part of the date, interval

Value	Abbreviation (SQL Server)	Access and ASP	Description
Year	Yy	yyyy	Year 1753 ~ 9999
Quarter	Qq	Q	Season 1 ~ 4
Month	Mm	M	Month 1 ~ 12
Day of the Year	Dy	Y	The number of days in a year, the day of the year 1-366
Day	Dd	D	Day, 1-31
Weekday	Dw	W	The number of days in a week, the day of the week 1-7
Week	Wk	Ww	Week, week of the year 0 ~ 51
Hour	Hh	H	Time 0 ~ 23
Minute	Mi	N	Minutes 0 ~ 59
Second	Ss	S	seconds 0 ~ 59
Millisecond	Ms	-	Milliseconds 0 ~ 999

SQL Server Date-time-to-string