SQL Server date and time to string

I. SQL Server Date and Time Functions
Date and time functions in SQL Server
1. Current System Date and Time
Select getdate ()

2. dateadd returns a new datetime value based on a period of time added to the specified date.
For example, add 2 days to the date
Select dateadd (day, 2, '2017-10-15 ') -- Return: 2004 00:00:00. 000

3. datediff returns the number of date and time boundaries across two specified dates.
Select datediff (day, '2017-09-01 ', '2017-09-18') -- Return: 17

4. datepart returns an integer representing the specified date of the specified date.
Select datepart (month, '2014-10-15 ') -- returns 10

5. Return the string representing the specified date part of the specified date by datename
Select datename (weekday, '2017-10-15 ') -- Return: Friday

6. Day (), month (), Year () -- compare it with datepart.

Select current date = convert (varchar (10), getdate (), 120)
, Current time = convert (varchar (8), getdate (), 114)

Select datename (DW, '2017-10-15 ')

Select the week of the current year = datename (Week, '2017-10-15 ')
, Today is the day of the week = datename (weekday, '2017-10-15 ')

Ii. Date Format Conversion

Select convert (varchar (100), getdate (), 0): 05 16 2006 AM
Select convert (varchar (100), getdate (), 1): 05/16/06
Select convert (varchar (100), getdate (), 2): 06.05.16
Select convert (varchar (100), getdate (), 3): 16/05/06
Select convert (varchar (100), getdate (), 4): 16.05.06
Select convert (varchar (100), getdate (), 5): 16-05-06
Select convert (varchar (100), getdate (), 6): 16 05 06
Select convert (varchar (100), getdate (), 7): 05 16, 06
Select convert (varchar (100), getdate (), 8): 10:57:46
Select convert (varchar (100), getdate (), 9): 05 16 2006 10: 57: 46: 827am
Select convert (varchar (100), getdate (), 10): 05-16-06
Select convert (varchar (100), getdate (), 11): 06/05/16
Select convert (varchar (100), getdate (), 12): 060516
Select convert (varchar (100), getdate (), 13): 16 05 2006 10: 57: 46: 937
Select convert (varchar (100), getdate (), 14): 10: 57: 46: 967
Select convert (varchar (100), getdate (), 20): 10:57:47
Select convert (varchar (100), getdate (), 21): 10:57:47. 157
Select convert (varchar (100), getdate (), 22): 05/16/06 10:57:47 AM
Select convert (varchar (100), getdate (), 23 ):
Select convert (varchar (100), getdate (), 24): 10:57:47
Select convert (varchar (100), getdate (), 25): 10:57:47. 250
Select convert (varchar (100), getdate (), 100): 05 16 2006 AM
Select convert (varchar (100), getdate (), 101): 05/16/2006
Select convert (varchar (100), getdate (), 102): 2006.05.16
Select convert (varchar (100), getdate (), 103): 16/05/2006
Select convert (varchar (100), getdate (), 104): 16.05.2006
Select convert (varchar (100), getdate (), 105): 16-05-2006
Select convert (varchar (100), getdate (), 106): 16 05 2006
Select convert (varchar (100), getdate (), 107): 05 16,200 6
Select convert (varchar (100), getdate (), 108): 10:57:49
Select convert (varchar (100), getdate (), 109): 05 16 2006 10: 57: 49: 437am
Select convert (varchar (100), getdate (), 110): 05-16-2006
Select convert (varchar (100), getdate (), 111): 2006/05/16
Select convert (varchar (100), getdate (), 112): 20060516
Select convert (varchar (100), getdate (), 113): 16 05 2006 10: 57: 49: 513
Select convert (varchar (100), getdate (), 114): 10: 57: 49: 547
Select convert (varchar (100), getdate (), 120): 10:57:49
Select convert (varchar (100), getdate (), 121): 10:57:49. 700
Select convert (varchar (100), getdate (), 126): 2006-05-16t10: 57: 49.827
Select convert (varchar (100), getdate (), 130): 18 ???? ?????? 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 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.

The value of interval is as follows:

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