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.

Style Standard SELECT (current date is 2017-05-09; time is 09:39:19.490 AM) Output
0 (default) Default for datetime and smalldatetime SELECT CONVERT(VARCHAR,GETDATE(),0) 05/09/2017
1 U.S. SELECT CONVERT(VARCHAR,GETDATE(),1) 05/09/17
2 ANSI SELECT CONVERT(VARCHAR,GETDATE(),2) 17.05.09
3 British/French SELECT CONVERT(VARCHAR,GETDATE(),3) 09/05/17
4 German SELECT CONVERT(VARCHAR,GETDATE(),4) 09.05.17
5 Italian SELECT CONVERT(VARCHAR,GETDATE(),5) 09-05-17
6 SELECT CONVERT(VARCHAR,GETDATE(),6) 09 May 17
7 SELECT CONVERT(VARCHAR,GETDATE(),7) May 09, 17
10 USA SELECT CONVERT(VARCHAR,GETDATE(),10) 05-09-17
11 JAPAN SELECT CONVERT(VARCHAR,GETDATE(),11) 17/05/09
12 ISO SELECT CONVERT(VARCHAR,GETDATE(),12) 170509
100 SELECT CONVERT(VARCHAR,GETDATE(),100) May 9 2017 9:39AM
101 U.S. SELECT CONVERT(VARCHAR,GETDATE(),101) 05/09/2017
102 ANSI SELECT CONVERT(VARCHAR,GETDATE(),2) 2017.05.09
103 British/French SELECT CONVERT(VARCHAR,GETDATE(),103) 09/05/2017
104 German SELECT CONVERT(VARCHAR,GETDATE(),104) 09.05.2017
105 Italian SELECT CONVERT(VARCHAR,GETDATE(),105) 09-05-2017
106 SELECT CONVERT(VARCHAR,GETDATE(),106) 09 May 2017
107 SELECT CONVERT(VARCHAR,GETDATE(),107) May 09, 2017
110 USA SELECT CONVERT(VARCHAR,GETDATE(),110) 05-09-2017
111 JAPAN SELECT CONVERT(VARCHAR,GETDATE(),111) 2017/05/09
112 ISO SELECT CONVERT(VARCHAR,GETDATE(),112) 20170509
Date and/or Time
8, 108 SELECT CONVERT(VARCHAR,GETDATE(),8) 09:39:19
9, 109 SELECT CONVERT(VARCHAR,GETDATE(),9) May 9 2017 9:39:19:490AM
13, 113 Europe default + milliseconds SELECT CONVERT(VARCHAR,GETDATE(),13) 09 May 2017 09:39:19: 490
14, 114 SELECT CONVERT(VARCHAR,GETDATE(),114) 09:39:00: 490
20, 120 ODBC canonical SELECT CONVERT(VARCHAR,GETDATE(),20) 2017-05-09 09:39:19
21, 121 ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset SELECT CONVERT(VARCHAR,GETDATE(),21) 2017-05-09 09:39:19.490
126 ISO8601 SELECT CONVERT(VARCHAR,GETDATE(),126) 2017-05-09T09: 39: 19.490
127 ISO8601 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.

Leave a Reply

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

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.