SQL Server Date time format conversion string explained

1. Date Time to String

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 (), 110): 05-16-2006
Select CONVERT (varchar), GETDATE (), 111): 2006/05/16
Select CONVERT (varchar), GETDATE (), 112): 20060516
Select CONVERT (varchar), GETDATE (), 113): 16 05 2006 10:57:49:513
Select CONVERT (varchar), GETDATE (), 114): 10:57:49:547
Select CONVERT (varchar), GETDATE (), 120): 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
Select CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

2. 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 ')

3. 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.

4. 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

SQL Server Date time format conversion string explained