Date loading problems

Rea_Antony · Post by **Rea_Antony** » Wed Jul 04, 2007 6:06 am

Hi,

I'm loading date columns from a sequential file-> transformer->table.

The input date is in the fomat DD/MM/YY

and the destination column is of datatype Timestamp.

I get the error: Invalid character value for cast specification.

I guess the error is because I'm loading a Date( without timestamp) into a Timestamp datatype column.

But my destination is SQL Server and I couldn't find a datatype 'Date'.
Also, I cannot use varchar to store the date as I need to perform date validations as well.

Can anyone suggest a way to overcome this error? That is, how can I load a value like 7/1/07 into a Timestamp column?

Thank you in advance.

thebird · Post by **thebird** » Wed Jul 04, 2007 7:34 am

Concatenate a time component - say " 00:00:00" with the date value and then convert this entire string into a Timestamp and load into the target timestamp field. Make sure to put in a space between the date and time components.

This should do it!

Rea_Antony · Post by **Rea_Antony** » Wed Jul 04, 2007 8:09 am

Thank you very much for the response.
I shall try concatenating the time stamp part " 00:00:00" to the date.

Rea_Antony · Post by **Rea_Antony** » Thu Jul 05, 2007 12:00 am

Hi ,

Since the input is in DD/MM/YY, i tried to convert it to the standard format for SQL Server before concatenating.

So, used formula
OCONV(ICONV("DSLink9.Field003","D2-DMY[2,2,2]"),"D2-MDY[2,2,4]"):" ":"00:00:00.000"

But I still get error:
Invalid character value for cast specification
DATE = " 00:00:00.000"

The target datatype is Timestamp 23,3

Can anyone explain why DS still doesnt consider the Date part?

On my part, I changed the target definition to varchar and put in ICONV("DSLink9.Field003","D2-DMY[2,2,2]") to check if atleast the Iconv is working ok.
But even though the job runs fine, the date column remians blank

Its the first time I'm handling Iconv\Oconv operations, so I would really appreciate some guidance on this

Thanks in advance....

chulett · Post by **chulett** » Thu Jul 05, 2007 12:05 am

Ditch the quotes around the Link.Field, they turn it from your incoming value to a literal string. And you only need one concatenation:

Code: Select all

OCONV(ICONV(DSLink9.Field003,"D2-DMY[2,2,2]"),"D2-MDY[2,2,4]"):" 00:00:00.000"

You sure your output date format shouldn't be YYYY-MM-DD? If this doesn't work for you, post your SQL in the target stage as well.

Rea_Antony · Post by **Rea_Antony** » Thu Jul 05, 2007 12:32 am

It worked it worked it worked :D :D :D
Thank you so much!

chulett · Post by **chulett** » Thu Jul 05, 2007 6:55 am

And yet my reply is rated 'Off Topic / Superfluous'... interesting. Oh well.

Glad that "worked worked worked" for you. :D

ray.wurlod · Post by **ray.wurlod** » Thu Jul 05, 2007 9:24 am

Get into the habit of renaming stages and links with meaningful names as soon as you place them in the design area, and renaming sequential file columns to meaningful names while on the Define tab of the Import wizard.

DSLink9.Field003 is anathema.