SQL-Date Query Error - Converting varchar to datetime results in exceeding value out of range

Question

I have a SQL query written by a colleague who is no longer here. The request is executed as part of an SSIS job, and from this month a failure has occurred with the following error:

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

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

Code for determining the time range 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 work is done twice a month. Once on the 16th for data from the 1st to the 15th 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 on the Internet, is using varchar for strStartDate the likely culprit? I'm not good enough with SQL to know how to replace everything that converts everything that happens there? Also, is there a better way to determine the end date of a month than just get the lead time? Any help at all would be greatly appreciated.

(PS, we run this task on SQL Server 2008 R2) And I checked with the database administrator and he did not say anything about the localization or regional settings that were changed on the SQL server.

Answer 1

There are many formats supported by SQL Server - see the MSDN Internet Books in CAST and CONVERT . Most of these formats are dependent on from what settings you have, so these settings can work several times, and sometimes not.

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

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

• YYYYMMDD only for dates (without time part); Pay attention here: no dash! , it is very important! YYYY-MM-DD NOT dependent on dateformat parameters on your SQL Server and will NOT work in all situations!

or

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

This is true for SQL Server 2000 and later.

If you use SQL Server 2008 or newer and the DATE data type (only DATE - not DATETIME !), You can also use the YYYY-MM-DD format and will also work with any settings on your SQL Server.

Also: when using SQL Server 2008, it is recommended that you use DATETIME2 (instead of DATETIME ), if at all possible. DATETIME2 parsing strings is much more forgiving for errors and / or different formats (e.g. AM AM / PM formatting, etc.).

Do not ask me why this whole topic is so complex and somewhat confusing - it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat parameter on your SQL Server.