Transform yyyy-m-d to yyyy-mm-dd

[de_conne]

Hi,

I'm new to DataStage and have an issue regarding a transform.
All my job has to do is load a .csv file into an SQL2000 tabel.

File consists of temperature measures by day:
2007-1-1; 30
2007-1-2; 34
2007-1-3; 29
2007-1-4; 24
...

Problem is that I encouter the following error message when trying to insert all values in the table:
"[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification"

I think I know the problem since only the records conaining a date of format yyyy-mm-dd are inserted.

So in my transform stage I should transform 2007-1-1 to 2007-01-01.

How should I transform this???

Thnks!!!!

[ArndW]

You would convert it to internal format, then back to display format.

Code: Select all

OCONV(ICONV(In.DateString,'D4-YMD'),'D4-YMD')

[de_conne]

Thx mate!

Not quite 100%, but very helpfull indeed

This did the trick:
OCONV(ICONV(DSlink1.date,'D4-YMD'),'D4-YMD[4,2,2]')

[WoMaWil]

de_conne, tell me the case, where this doesn't work and Arnd doesn't get his 100 %

[ray.wurlod]

Arnd left out the "[4,2,2]" which might result in a single digit month or day number.

Just for laughs here's an alternative.

Code: Select all

Fmt(Fmt(Field(InLink.TheDate,"-",1,1),"R%4") : Fmt(Field(InLink.TheDate,"-",2,1),"R%2") : Fmt(Field(InLink.TheDate,"-",3,1),"R%2"), "L####-##-##")