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.

--Today
SELECT GETDATE() 'Today'
--Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
--First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
--Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
--First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
--Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
--First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
--Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
--First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
--Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
--First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
--Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
--First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
--Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'

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

SELECT CONVERT(DATETIME,CONVERT(DATE,GETDATE())) 'Today 00:00:00.000' 
SELECT DATEADD(MILLISECOND,-3,CONVERT(DATETIME,DATEADD(DAY,1,CONVERT(DATE,GETDATE())))) 'Today 23:59:59.997'

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. An 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 of outputs.

SELECT REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') -- 05092017
SELECT REPLACE(CONVERT(VARCHAR, GETDATE(),102),'.','') -- 20170509

SELECT REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') -- 05092017093919
SELECT REPLACE(CONVERT(VARCHAR, GETDATE(),102),'.','') + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') -- 20170509093919

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