Some stuff with Date and Time in SQL Server

I’m writing this post because I want to put some more stuff around dates and times in one place. I additionally teach SQL Server classes and often come up to talk about dates and times, and converting them to string representations. Of course I don’t always have time enough to examine all the cases, so I’ll be referencing this post to my slides as well.

Next table shows the conversion from date and time to string. Note that the default length for VARCHAR when it’s used in a CAST/CONVERT function is 30. But when you declare a variable as VARCHAR only, then the default length is 1. The longest conversion of a date and time together doesn’t have more than 30 characters and that’s why the default is fine for the dates in the CONVERT function.

StyleStandardSELECT (current date is 2017-05-09; time is 09:39:19.490 AM)Output
0 (default)Default for datetime and smalldatetimeSELECT CONVERT(VARCHAR,GETDATE(),0)05/09/2017
1U.S.SELECT CONVERT(VARCHAR,GETDATE(),1)05/09/17
2ANSISELECT CONVERT(VARCHAR,GETDATE(),2)17.05.09
3British/FrenchSELECT CONVERT(VARCHAR,GETDATE(),3)09/05/17
4GermanSELECT CONVERT(VARCHAR,GETDATE(),4)09.05.17
5ItalianSELECT CONVERT(VARCHAR,GETDATE(),5)09-05-17
6SELECT CONVERT(VARCHAR,GETDATE(),6)09 May 17
7SELECT CONVERT(VARCHAR,GETDATE(),7)May 09, 17
10USASELECT CONVERT(VARCHAR,GETDATE(),10)05-09-17
11JAPANSELECT CONVERT(VARCHAR,GETDATE(),11)17/05/09
12ISOSELECT CONVERT(VARCHAR,GETDATE(),12)170509
100SELECT CONVERT(VARCHAR,GETDATE(),100)May 9 2017 9:39AM
101U.S.SELECT CONVERT(VARCHAR,GETDATE(),101)05/09/2017
102ANSISELECT CONVERT(VARCHAR,GETDATE(),2)2017.05.09
103British/FrenchSELECT CONVERT(VARCHAR,GETDATE(),103)09/05/2017
104GermanSELECT CONVERT(VARCHAR,GETDATE(),104)09.05.2017
105ItalianSELECT CONVERT(VARCHAR,GETDATE(),105)09-05-2017
106SELECT CONVERT(VARCHAR,GETDATE(),106)09 May 2017
107SELECT CONVERT(VARCHAR,GETDATE(),107)May 09, 2017
110USASELECT CONVERT(VARCHAR,GETDATE(),110)05-09-2017
111JAPANSELECT CONVERT(VARCHAR,GETDATE(),111)2017/05/09
112ISOSELECT CONVERT(VARCHAR,GETDATE(),112)20170509
Date and/or Time
8, 108SELECT CONVERT(VARCHAR,GETDATE(),8)09:39:19
9, 109SELECT CONVERT(VARCHAR,GETDATE(),9)May 9 2017 9:39:19:490AM
13, 113Europe default + millisecondsSELECT CONVERT(VARCHAR,GETDATE(),13)09 May 2017 09:39:19: 490
14, 114SELECT CONVERT(VARCHAR,GETDATE(),114)09:39:00: 490
20, 120ODBC canonicalSELECT CONVERT(VARCHAR,GETDATE(),20)2017-05-09 09:39:19
21, 121ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffsetSELECT CONVERT(VARCHAR,GETDATE(),21)2017-05-09 09:39:19.490
126ISO8601SELECT CONVERT(VARCHAR,GETDATE(),126)2017-05-09T09: 39: 19.490
127ISO8601 with time zone Z.SELECT CONVERT(VARCHAR,GETDATE(),127)2017-05-09T09: 39: 19.490

 

However, when using defaults it could be very difficult to find out errors caused by them. In this demo I used the default for VARCHAR in order to shorten the queries.

Next are some useful queries when working with dates and date ranges. They often come handy in the T-SQL codes. Combinations of the DATEADD and DATEDIFF functions allows us to derive very interesting dates. Queries, self-descriptive, are given below.

If you want to have the today’s very beginning and very ending moments up to precision of millisecond, with T-SQL you’re doing it in the following way for example:

Note that there is also a function GETUTCDATE() that always gives the Universal Coordinated Time and can also be used in all the above queries.

Converted dates to strings are often used in REPLACE functions to avoid separators and to obtain another format of a date. Example is when you want to convert a date into a sting-integer, or additionally to append the time to the sting-integer. Next are some examples with outputs.

And, of course you can make many more combinations and obtain the desired dates and formatted to string representations.

Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *

*