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.

 

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 *

*