CODEWITHSUNDEEP
sqlt-sqldatetimessis
Oryx
Oryx

Reputation: 302

SSIS/SQL - Convert Date/Time to String Failing

I have a basic SSIS job that runs a SQL query and exports the results of the query to an Excel file. The SSIS job has run without issue for over a year, but has started to fail (at validation phase, before execution) this month with the following error:

"Conversion Failed when converting date and/or time from character string."

The SQL query itself is a simple SELECT with a WHERE clause to specify a start and end date range to gather records from. The part of the query that determines the date 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(@RunDateTime AS DATE)

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 = CAST(@Year AS VARCHAR) + CAST(@Month AS VARCHAR) + '16'
                    SET @StartDate = CONVERT(datetime, @strStartDate)
                    SET @EndDate = @RunDate
    END

I can take this code out of SSIS and execute it directly on the server via SSMS, and it works correctly with no errors. However, the SSIS job cannot run it. In fact, I can't even go into the OLE DB Source object for the SQL command and view the column metadata anymore, I get the error mentioned above every time.

I have found that by commenting out the SET @StartDate = CONVERT(datetime, @strStartDate) line the query works again in SSIS, but there's no reason this line should cause problems. As I said, it has worked for over a year with no issues. I'm literally at my wits' end with this, and cannot understand why the job has suddenly stopped working.

I did check with my DBA, and he assures me nothing has been changed on the SQL server side, but then again I wouldn't even know where to ask him to look.

Other info:

Upvotes: 0

Views: 2661

Answers (1)

Ciarán
Ciarán

Reputation: 3057

This fails because the @strStartDate is not a valid date or is not in a recognized format. It shouldn't fail in November, December or January but it will definitely fail for February through October (Month(@RunDate) - 1) because the CAST(@Month AS VARCHAR) returns only a single digit.

To be honest this is is a rather long way of calculating the 16th of the previous month, can you change it to...

DECLARE @StartDate DateTime

IF DATEPART(d, GetDate()) = 16
   SET @StartDate = DateAdd(mm, DateDiff(mm,0,GetDate()),0)
ELSE
   SET @StartDate = DateAdd(mm, DateDiff(mm,0,GetDate())-1,0)+15;

So much easier

Upvotes: 2

Related Questions