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 - need help!

Hi Guys,
Need some help.
I have a sql script generated in oracle consisting of insert into values and
I'm trying to run that script in SQL Server 2k, but it has a TO_Date
function and I'm unable to run it. I looked on the web for a work around and
didn't come up with anything.
below is a sample script which I'm trying to run in SQL Server. Thanks.
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', LINDA KAY ', CAST( '03/09/1992 12:00:00
AM', 'MM/DD/YYYY HH:MI:SS AM')
, CAST ( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M', CAST(
'04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, CAST( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);oops sorry, I copied the script I was working around, here is the original
script.
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', 'LINDA KAY', TO_Date( '03/09/1952
12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M',
TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS
AM'), 0, 0);
"CoolHandSid" <sid@.handluke.com> wrote in message
news:eca5bMpGFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hi Guys,
> Need some help.
> I have a sql script generated in oracle consisting of insert into values
and
> I'm trying to run that script in SQL Server 2k, but it has a TO_Date
> function and I'm unable to run it. I looked on the web for a work around
and
> didn't come up with anything.
> below is a sample script which I'm trying to run in SQL Server. Thanks.
> INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME,
ATTEND_NAME,
> BIRTH_DATE,
> MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
> SECURITY_LVL ) VALUES (
> 504210, 2031806529, 1, '361384843', LINDA KAY ', CAST( '03/09/1992
12:00:00
> AM', 'MM/DD/YYYY HH:MI:SS AM')
> , CAST ( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M', CAST(
> '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
> , 79807613, 9, CAST( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM'),
> 0, 0);
>
>|||Use one of the following forms for dates.
'20050131' -- Just the date
'2005-01-31T17:59:00' -- Date/hours/minutes/seconds
'2005-01-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
Note that the 'T' is required with the date and time forms otherwise
the result is sensitive to regional date format settings.
David Portas
SQL Server MVP
--|||David,
Thanks for the reply.
The problem is I have a file with 800,000 lines with TO_Date which was
geneated from a oracle DBA. I'm trying to get that into a table in SQL
Server. Can you suggest some user defined function so I can run it faster?
Thanks.
Again the sample script is as follows,
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', 'LINDA KAY ', TO_Date( '03/09/1952
12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M',
TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS
AM'), 0, 0);
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109265282.722910.17120@.f14g2000cwb.googlegroups.com...
> Use one of the following forms for dates.
> '20050131' -- Just the date
> '2005-01-31T17:59:00' -- Date/hours/minutes/seconds
> '2005-01-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
> Note that the 'T' is required with the date and time forms otherwise
> the result is sensitive to regional date format settings.
> --
> David Portas
> SQL Server MVP
> --
>|||Not fully tested but it seems to work with what you posted.
CREATE FUNCTION dbo.to_date
(@.dt VARCHAR(50), @.dt_format VARCHAR(50))
RETURNS DATETIME
AS
BEGIN
RETURN
CONVERT(DATETIME,
CONVERT(CHAR(12),
CAST(
SUBSTRING(@.dt,PATINDEX('%YYYY%',@.dt_form
at),4)+
SUBSTRING(@.dt,PATINDEX('%MM%',@.dt_format
),2)+
SUBSTRING(@.dt,PATINDEX('%DD%',@.dt_format
),2) AS DATETIME)
,0)+
SUBSTRING(@.dt,PATINDEX('%HH%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%MI%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%SS%',@.dt_format
),2)+' '+
SUBSTRING(@.dt,PATINDEX('%AM%',@.dt_format
),2),9)
END
You'll need to search and replace "TO_DATE" with "dbo.to_date" first.
David Portas
SQL Server MVP
--|||David,
Thanks a lot, that worked like a charm.
Thanks!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109268237.382512.109560@.o13g2000cwo.googlegroups.com...
> Not fully tested but it seems to work with what you posted.
> CREATE FUNCTION dbo.to_date
> (@.dt VARCHAR(50), @.dt_format VARCHAR(50))
> RETURNS DATETIME
> AS
> BEGIN
> RETURN
> CONVERT(DATETIME,
> CONVERT(CHAR(12),
> CAST(
> SUBSTRING(@.dt,PATINDEX('%YYYY%',@.dt_form
at),4)+
> SUBSTRING(@.dt,PATINDEX('%MM%',@.dt_format
),2)+
> SUBSTRING(@.dt,PATINDEX('%DD%',@.dt_format
),2) AS DATETIME)
> ,0)+
> SUBSTRING(@.dt,PATINDEX('%HH%',@.dt_format
),2)+':'+
> SUBSTRING(@.dt,PATINDEX('%MI%',@.dt_format
),2)+':'+
> SUBSTRING(@.dt,PATINDEX('%SS%',@.dt_format
),2)+' '+
> SUBSTRING(@.dt,PATINDEX('%AM%',@.dt_format
),2),9)
> END
> You'll need to search and replace "TO_DATE" with "dbo.to_date" first.
> --
> David Portas
> SQL Server MVP
> --
>