Extra characters at the end of a datetime or interval

[Das]

I am getting the following message in Datastage Director "Extra characters at the end of a datetime or interval".

Incoming date format is: 2000-09-07 09:26:27.000 ,i want to convert it to 2000-09-07 09:26:27 for comparing the date field with the date field in the table. Please provide a solution

[ray.wurlod]

Code: Select all

Left(TheTimestamp, 19) 

or

Code: Select all

Field(TheTimestamp, ".", 1, 1)

[DSguru2B]

Also, try to change both formats using ICONV and then do the comparison, IMHO. I am not a big fan of date string comparisons.

[ray.wurlod]

I'm a big fan of efficiency and ease of maintenance.

All that's required is to lose the milliseconds from a timestamp.

Iconv() and Oconv() don't work with timestamps, so that you'd need to do some string manipulation in any case.

[DSguru2B]

What type of database are you dealing with? What is the length of timestamp there? If it supports milliseconds, then i would say keep your milliseconds in tact if your going for a string comparison.
In the end, 2000-09-07 09:26:27.000 is different than
2000-09-07 09:26:27.003

[Das]

Code: Select all

Left(TheTimestamp, 19) 

or

Code: Select all

Field(TheTimestamp, ".", 1, 1)

...

Hi Ray,
Thanks.

This is working fine.But i dont know why 000 is appended at the end of the time stamp ,As it is declared timestamp 19 in source and target and bith are same table.

[/b]

[I_Server_Whale]

What IS the database that you are dealing with? Is it SQL Server?

Whale.

[ray.wurlod]

It's not DataStage that's appending the "000" - it's your SELECT statement. Try it in a different client.