DSXchange

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.

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!

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

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....

Ditch the quotes around the Link.Field, they turn it from your incoming value to a literal string. And you only need one concatenation:
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.

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

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

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

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.