Don't use 'SET DATEFORMAT' and don't manipulate dates as strings

There are two things that're a bad idea when it comes to dates and SQL Server (that I'm blogging about right now, there are surely many more than that!), using 'SET DATEFORMAT' to alter the default date format that's used and manipulating them as strings. It's fair to say that manipulating dates as strings is pretty much always a bad idea, but this post is about SQL Server so let's focus on that. I recently had cause to rebuild a database server that had failed, restoring databases from backups, setting up linked-servers and re-adding logins. Unfortunately the latter two had to be done "long hand" as there wasn't a build document for the server in question which meant that the fact that one of the logins relied on being configured to have 'SET DATEFORMAT DMY' (i.e. UK date format) which caused a few niggles....

Doing it in a way that can break

Here's a query we discovered which is responsible for setting the Start/End dates used to generate a report to the first and last days of the previous month:

DECLARE @FromDate DATETIME, @ToDate DATETIME

DECLARE @FirstDayOfCurrentMonth datetime  
SET @FirstDayOfCurrentMonth = '01' + RIGHT(CONVERT(varchar(10), getdate(), 103), 8)
	
SET @ToDate = DATEADD(d,-1,@FirstDayOfCurrentMonth)  
SET @FromDate = '01' + RIGHT(CONVERT(VARCHAR(10), @ToDate, 103), 8)   

SELECT	@FromDate [FromDate], 
	@ToDate [ToDate]

As of the time of writing (early September), running this query over a connection to SQL Server that doesn't have 'SET DATEFORMAT DMY' set results in FromDate being returned as '2018-01-01 00:00:00.000' and ToDate being returned as '2018-01-08 00:00:00.000' neither of which are the first and last days of August! Picking the query apart, this is because this line:

SET @FirstDayOfCurrentMonth = '01' + RIGHT(convert(varchar(10), getdate(), 103), 8)

Does the following things:

1. CONVERT(varchar(10), getdate(), 103) - Gets the current date in format 103, which is dd/mm/yyyy and referred to as the "British/French" standard: 05/09/2018
2. RIGHT(CONVERT(varchar(10), getdate(), 103), 8) - Takes the right-most 8 characters of the formatted date: /09/2018
3. '01' + RIGHT(CONVERT(varchar(10), getdate(), 103), 8) - Prefixes the string with 01, giving: 01/09/2018 (which is indeed the current day of the month!)
4. SET @FirstDayOfCurrentMonth = ....... - This is where it goes wrong!

Because the date format hasn't been set to DMY the string '01/09/2018' gets treated as being in MM/DD/YYYY format meaning that when it's parsed and turned into a DATETIME (as that's the datatype that @FirstDayOfCurrentMonth is declared as) it becomes '2018-01-09 00:00:00.000'. All of a sudden we've flown back in time to January 9th!

This problem then propogates through the setting of @ToDate and @FromDate:

1. @ToDate - DATEADD is used to deduct one day from the date, giving us the final result of '2018-01-08 00:00:00.000', at least it's using DATEADD to do date math though!
2. @FromDate - The same conversion, sub-stringing and concatenation happens so:
   1. CONVERT(VARCHAR(10), @ToDate, 103) - Gives us '08/01/2018'
   2. RIGHT(.......) - Gives us '/01/2018'
   3. '01' + ...... - Gives us '01/01/2018'

As it turns out, when this gets converted back to a date, the flipping of the month and the year has no effect (as they're both '01'), but we still end up with the wrong result because the error has been flowed through from the initial generation of @FirstDayOfCurrentMonth.

Doing it right

There's doubtless a thousand ways to do this right, but when it comes to solving this problem the key thing is to never treat the date as a string. By using DATEADD and DATEPART and any othr SQL intrinsic that treats the date as well, a date, you know that changes in the string representation of the date won't impact the result. It's likely that it's probably more performant as well! Here's one example (not necessarily the best!) of how to get the first and last day of the previous month in SQL without recourse to string manipulation:

DECLARE @now DATETIME = GETDATE()
SELECT	CAST(DATEADD(d, 1, DATEADD(m, -1, DATEADD(d, -DATEPART(day, @now), CAST(@now AS DATE)))) AS DATETIME) [FromDate],
	DATEADD(ms, -3, CAST(DATEADD(d, 1, CAST(DATEADD(d, -DATEPART(day, @now), @now) AS DATE)) AS DATETIME)) [ToDate]

These are both quite long-winded chunks of code, so here's the ToDate (the end date of last month) de-constructed a little:

SELECT	DATEPART(day, @now) [NumberOfDaysInToday],
	DATEADD(d, -DATEPART(day, @now), @now) [TodayWithDaysRemoved],
	CAST(DATEADD(d, -DATEPART(day, @now), @now) AS DATE) [JustTheDateFromTheAbove],
	DATEADD(d, 1, CAST(DATEADD(d, -DATEPART(day, @now), @now) AS DATE)) [AddADayBack],
	CAST(DATEADD(d, 1, CAST(DATEADD(d, -DATEPART(day, @now), @now) AS DATE)) AS DATETIME) [AddTimeBackIn],
	DATEADD(ms, -3, CAST(DATEADD(d, 1, CAST(DATEADD(d, -DATEPART(day, @now), @now) AS DATE)) AS DATETIME)) [ToDate]

So, what's happening here? Well, working down through each of the components:

1. The first part gets the number of days in todays date, 5
2. The next part deducts those days from the date, turning 2018-09-05 01:30:45 into 2018-08-31 01:30:45
3. Then we strip out the time part of the date, for now, giving us 2018-08-31
4. Then we add a day back, giving us 2018-09-01
5. Then we add the time back into the date, giving us 2018-09-01 00:00:00.000
6. Finally we take off 3 milliseconds to drop us right at the end of the day before, et voila! 2018-08-31 23:59:59.997

Doing it simpler

Notice how at step 5 we've already got the start of this month? That means we could simplify the code for getting the first, and last, day of last month slightly by saying:

DECLARE @now DATETIME = GETDATE(), @fromDate DATETIME, @toDate DATETIME, @firstDayOfCurrentMonth DATETIME

SET @firstDayOfCurrentMonth = DATEADD(d, 1, CAST(DATEADD(d, -DATEPART(day, @now), @now) AS DATE))
SET @fromDate = DATEADD(month, -1, @firstDayOfCurrentMonth)
SET @toDate = DATEADD(ms, -3, @firstDayOfCurrentMonth)

SELECT	@fromDate [FromDate],
	@toDate [ToDate]

Yes, this is a bit longer than the code I've shown just under the heading but it's easier to reason about overall as the number of explicit casts are reduced (we don't have to cast back to DATETIME explicitly when setting @firstDayOfCurrentMonth) and the only complicated bit of logic is where we're working out @firstDayOfCurrentMonth; the calculations for both @fromDate and @toDate are pretty simple and clear