SQL Server Date time format conversion string explained

In SQL server databases, SQL Server DateTime format conversion strings can change the format of SQL Server date and time, which is what every SQL database user should know. In this article we mainly introduce the SQL Server date-time to the string of knowledge, then let us look at this part of the content.

Date Time to string:

2. Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am
4. Select CONVERT (varchar), GETDATE (), 1): 05/16/06
6. Select CONVERT (varchar), GETDATE (), 2): 06.05.16
8. Select CONVERT (varchar), GETDATE (), 3): 16/05/06
10. Select CONVERT (varchar), GETDATE (), 4): 16.05.06
12. Select CONVERT (varchar), GETDATE (), 5): 16-05-06
14. Select CONVERT (varchar), GETDATE (), 6): 16 05 06
16. Select CONVERT (varchar), GETDATE (), 7): 05 16, 06
18. Select CONVERT (varchar), GETDATE (), 8): 10:57:46
20. Select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827am
22. Select CONVERT (varchar), GETDATE (), 10): 05-16-06
24. Select CONVERT (varchar), GETDATE (), 11): 06/05/16
26. Select CONVERT (varchar), GETDATE (), 12): 060516
28. Select CONVERT (varchar), GETDATE (), 13): 16 05 2006 10:57:46:937
30. Select CONVERT (varchar), GETDATE (), 14): 10:57:46:967
32. Select CONVERT (varchar), GETDATE (), 20): 2006-05-16 10:57:47
34. Select CONVERT (varchar), GETDATE (), 21): 2006-05-16 10:57:47.157
36. Select CONVERT (varchar), GETDATE (): 05/16/06 10:57:47 AM
38. Select CONVERT (varchar), GETDATE (), 23): 2006-05-16
40. Select CONVERT (varchar), GETDATE (), 24): 10:57:47
42. Select CONVERT (varchar), GETDATE (), 25): 2006-05-16 10:57:47.250
44. Select CONVERT (varchar), GETDATE (), (+): 2006 10:57am
46. Select CONVERT (varchar), GETDATE (), 101): 05/16/2006
48. Select CONVERT (varchar), GETDATE (), 102): 2006.05.16
50. Select CONVERT (varchar), GETDATE (), 103): 16/05/2006
52. Select CONVERT (varchar), GETDATE (), 104): 16.05.2006
54. Select CONVERT (varchar), GETDATE (), 105): 16-05-2006
56. Select CONVERT (varchar), GETDATE (), 106): 16 05 2006
58. Select CONVERT (varchar), GETDATE (), 107): 05 16, 2006
60. Select CONVERT (varchar), GETDATE (), 108): 10:57:49
62. Select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437am
64. Select CONVERT (varchar), GETDATE (), 110): 05-16-2006
66. Select CONVERT (varchar), GETDATE (), 111): 2006/05/16
68. Select CONVERT (varchar), GETDATE (), 112): 20060516
70. Select CONVERT (varchar), GETDATE (), 113): 16 05 2006 10:57:49:513
72. Select CONVERT (varchar), GETDATE (), 114): 10:57:49:547
74. Select CONVERT (varchar), GETDATE (), 120): 2006-05-16 10:57:49
76. Select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700
78. Select CONVERT (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827
80. Select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907am

In SQL database, there is a class of functions that have to be mentioned, that is, SQL Server datetime function, which is the most common SQL Server processing date and time functions, the following will be described in this kind of function.

SQL Server date and time functions:

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 this year how many weeks =datename (week, ' 2004-10-15 '), today is the week of =datename (weekday, ' 2004-10-15 ')

SQL Server Date 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 in the interval specified date2-date1

DATEADD (interval,number,date) with the date specified in interval, plus number

DatePart (interval,date) returns the integer value corresponding to the specified portion of the date, interval

Datename (interval,date) returns the name of the string that corresponds to the specified part of the date, interval

The setting values for the parameter interval are as follows:

Description of the value abbreviation (SQL Server) (Access and ASP)

Year Yy yyyy 1753 ~ 9999

Quarter Qq Q Season 1 ~ 4

Month Mm M 1 ~ 12

Day of the year Dy y the number of days of the year, the day of the first 1-366

Day Dd D, 1-31

Weekday Dw W Day of the week, day of the week 1-7

Week Wk WW Week, week of the year 0 ~ 51

Hour Hh H 0 ~ 23

Minute Mi N min 0 ~ 59

Second Ss s seconds 0 ~ 59

Millisecond MS-MS 0 ~ 999

In Access and ASP, the system date time is obtained with date () and now (), where Datediff,dateadd,datepart is also used in Access and ASP, and the usage of these functions is similar.

Examples of SQL Server date functions:

1.GetDate () for SQL Server:select GetDate ()

2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds

DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days

3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 that is Monday (Sunday is 1, Saturday is 7)

DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25, or 25th

DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 Day of the Year

DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 that is 2005

The knowledge about SQL Server date time format conversion string is introduced here, I hope this introduction can have a harvest for you!

SQL Server Date time format conversion string explained