SQL - Date Query Issue - varchar to datetime conversion resulted in out-of-range value

Question

I have a SQL query written by a colleague who is no longer here. The query runs as part of an SSIS job, and as of this month has started failing with the following error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The query itself is just a basic select with a where clause that looks for values in a certain time range (the date range between @startdate and @enddate)

The code to determine the time range is below:

DECLARE     @RunDateTime datetime
DECLARE     @RunDate datetime
DECLARE     @StartDate datetime
DECLARE     @EndDate datetime
DECLARE     @Month int
DECLARE     @Year int
DECLARE     @strStartDate varchar(10)

SET     @RunDateTime = GetDate()
SET     @RunDate = cast(round(convert(real, @RunDateTime),0,1) as datetime)

IF                  DATEPART(d, @RunDate) = 16
    BEGIN       
                    SET @StartDate =  DATEADD(d, -15, @RunDate)
                    SET @EndDate = @RunDate
    END
ELSE
    BEGIN
                    IF      Month(@RunDate) = 1
                            SET @Month = 12
                    ELSE    
                            SET @Month = Month(@RunDate) - 1

                    IF      Month(@RunDate) = 1
                            SET @Year = Year(@RunDate) - 1
                    ELSE
                            SET @Year = Year(@RunDate)

                    SET @strStartDate = CONVERT(varchar(2), @Month)+ '/16/' + CONVERT(varchar(4), @Year)
                    SET @StartDate = CONVERT(datetime, @strStartDate, 101)
                    SET @EndDate = @RunDate
    END

This job runs twice a month. Once on the 16th for data from the 1st to the 15th of the month, and once on the 1st of the next month for data from the 16th to the end of the previous month.

From what I can find online, the use of the varchar for strStartDate is the likely culprit? I'm not familiar enough with SQL to know how to replace all that convert stuff that's going on there? Also, is there a better way to determine the end of the month date than just getting the run time? Any help at all would be greatly appreciated.

(PS, we run this job on SQL Server 2008 R2) And I checked with the DBA and he said nothing about localization or regional settings has changed on the SQL server.

Answer 1

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependant on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

• YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

• YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

Also: with SQL Server 2008, it is recommended to use DATETIME2 (instead of DATETIME) if at all ever possible. DATETIME2 parsing of strings is a lot more forgiving for error and/or different formats (like US AM/PM formatting etc.) .

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.