Date out of range?!

Greetings!

I have a data source that gets generated based on a variable with the following SQL :

"select * from result where deletion_ind = 1 and when_deleted >= '" + (dt_str, 50, 1252) @.[User::Last_Run_Date] + "'"

But when I run my package I get an error message saying:

The conversion of CHAR to DATETIME resulted in a DATETIME value out of range

The Last_Run_Date variable is set to '2006-06-25 14:35:05.450'

When I run the code in a QA session it works, but in the package it complains! What am I doing wrong?

Thanks for your help in advance.

Does the error get thrown by SSIS eval;uating the expression or by SQL Server when it gets executed?

If the latter...use SQL Profiler to check what is getting issued.

-Jamie

|||

Does the error get thrown by SSIS eval;uating the expression or by SQL Server when it gets executed?

The expression evaluates correctly. The error occurs when I run the package.

|||

Yeah I know that. I mean when it actually fails, is it the expression evaluation that fails or the issuing of the SQL? Can you post the full error message?

Just cos the expression evaluates successfully at design-time it does not necassarily mean it will do at execution-time.

-Jamie

|||

Hi Jamie,

The error message I get is:

Error: 0xC0202009 at Load Deleted Data, Result on DevTill [1]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "The conversion of CHAR to DATETIME resulted in a DATETIME value out of range.".

Error: 0xC004706B at Load Deleted Data, DTS.Pipeline: "component "Result on DevTill" (1)" failed validation and returned validation status "VS_ISBROKEN".

Error: 0xC004700C at Load Deleted Data, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Load Deleted Data: There were errors during task validation.

Warning: 0x80019002 at Route A: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Result Transformation: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Result Transformation Faster Version.dtsx" finished: Failure.

|||

Hmmm...at what point does it fail? Is it when the Execute SQL Task executes or is it when the package is getting validated (which is the very first thing that happens - before any tasks have executed.

-Jamie

|||

Hmmm...at what point does it fail? Is it when the Execute SQL Task executes or is it when the package is getting validated (which is the very first thing that happens - before any tasks have executed.

While the package is being validated.

By the way, the variable User::Last_Run_Date is a datetime variable. In my SQL string (the one used to generate the data source) am casting my datatime variable as a character. But I can't see why that would cause a problem.

|||

Just a guess, but what is in your variable when you deploy the package?

I dare say that this will succeed if you set DelayValidation=TRUE on the Execute SQL Task.

-Jamie

|||

Hi Jamie,

The error does not occur while the SQL Task is being validated. The Task correctly picks the right date and assigns it to my variable. The variable then gets used in a Data source task. It's at the point of validating/running the datasource part of my Data Flow component that I get the error message.

|||

Then there is something wrong in the SQL. Use Profiler to see what is getting issued against SQL Server.

Or, use this technique to get the SQL statement in a watch window: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie

|||

Is it possible that your client - SSIS - is using a different locale from the server? I have seen run into this occasionally as my home PC uses a UK locale.

If so, then you may find that running the query in QA succeeds, but the client fails. But you'll need to work on it quickly - in a few days (1/7, or 7/1 depending on your locale) it may magically look like it is working again.

Donald

|||

Hi Donald,

Yes that is a possibility but how to change the date format in SSIS?!

|||

Donald is referring to the locale if your client workstation on which SSIS is installed.

However, this is a murky area. You should read this post: http://blogs.conchango.com/jamiethomson/archive/2006/04/26/3870.aspx and be aware of it.

Did you use Profiler to check what was getting issued? Profiler is always the first place to start in these situations - I can't stress just how useful a tool it is.

-Jamie

|||

Hi Jamie,

I am not quite sure how to use profiler for this problem.

I have a data source coming from SQL Server 6.5. The data source is generated from a variable with the following expression :

"select * from result where deletion_ind = 1 and when_deleted >= '" + (dt_str, 50, 1252) @.[User::Last_Run_Date] + "'"

The @.[User::Last_Run_Date] is a datetime variable

When I run it, I get the error about not being able to convert CHAR to DATETIME. The value of my Last_Run_Date is 2006-06-25 14:35:05.450

How am I suppose to use profiler for this?!

|||

You've already told me further up this thread that the error is getting thrown by SQL Server when you issue that query from SSIS. You have 2 ways of finding out what query SSIS is issuing after it has evaluated that expression:

1) Use profiler. However this is the first time you have mentioned that it is SQL Server 6.5. I don't actually know if Profiler works for SQL 6.5 though I assume it does.

2) Put the query into a variable and use a watch window

Both of which I've already mentioned in this thread and I gave you a link for option 2.

-Jamie

date format, select convert(varchar(8), getdate(), 1)

With this clause "select convert(varchar(16), getdate(), 101)" I can get mmddyyyy but

How can I get time such as mmddyyyyhhmm

Thanks
Daniel

I do not believe there is a standard format that will give you exactly what you are wanting. Here is how to do it using DATEPART and REPLICATE to pad the items out.

Code Snippet

SELECT REPLICATE('0', 2-LEN(DATEPART(mm, getdate()))) + CAST(DATEPART(mm, getdate()) AS VARCHAR(2)) + REPLICATE('0', 2-LEN(DATEPART(dd, getdate()))) + CAST(DATEPART(dd, getdate()) AS VARCHAR(2)) + CAST(DATEPART(YY, getdate()) AS VARCHAR(4)) + REPLICATE('0', 2-LEN(DATEPART(hh, getdate()))) + CAST(DATEPART(hh, getdate()) AS VARCHAR(2)) + REPLICATE('0', 2-LEN(DATEPART(mi, getdate()))) + CAST(DATEPART(mi, getdate()) AS VARCHAR(2))

|||

select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

This will get the format of hh:mms (108) and you just replace the : with nothing and take the last 2 characters off.

|||

Very good

I have mmddyyyyhhmm but a little error

I get 04/19/20071223

should be 04/19/2007 12:23

Thanks
Daniel

|||

Code Snippet

create function dbo.fn_FormatADate(

@.dtInput datetime

)

returns char(13)

as

begin

return replace(Replace( convert(varchar,@.dtInput,101) + left(convert(varchar,@.dtInput,108),5) ,'/',''),':','')

end

GO

select dbo.fn_FormatADate(getdate())

GO

|||

select convert(varchar(16), getdate(), 101)+' '+LEFT(convert(varchar, getdate(), 108),5)

That should give you the format that you illustrated in your "should be".

|||

Hi Ben,

Glad to see you here.

Regards,

Alejandro Mesa

|||

Just a thought...since we always know the len of the output, replicate is a bit more combersome than using "RIGHT"...

Code Block

declare @.TheDate datetime

set @.TheDate = GetDate()

select

right('0' + convert(varchar,datepart(Month,@.TheDate)), 2) --The Month

+ right('0' + convert(varchar,datepart(Day,@.TheDate)), 2) --The Day

+ convert(varchar,datepart(Year,@.TheDate)) --For the Year

+ right('0' + convert(varchar,datepart(hour,@.TheDate)), 2) --The hour

+ right('0' + convert(varchar,datepart(minute,@.TheDate)), 2) --The minute