Combining date and time fields to create a timestamp
Posted: Wed Oct 03, 2001 7:26 am
Hello all,
I am running datastage 4.1 on Windows 2000 with SQL server 2000. I have a flat file containing a date field in the format yyyymmdd and a time field in the format "hhmmss". What I want to do is take these two fields and merge them into a single datetime field in the format yyyy-mm-dd hh:mm:ss . I have tried using iconv and oconv, this is my best effort so far
Cats(Cats(Cats(Oconv(Iconv(DSLink3.call_date, "D"), "D-YMD"), " "), Oconv(Iconv(Cats(Cats(Cats(Cats(Left(DSLink3.call_time,2), ":"), Left(Right(DSLink3.call_time,4),2)), ":"), Right(DSLink3.call_time,2)), "MTS"), "MTS")),".000")
I have no problem outputting the string created to a flat file, but cannot convert it into a format that will be accepted by my database. Any hints or suggestions appreciated.
Regards,
Simon Vollett
I am running datastage 4.1 on Windows 2000 with SQL server 2000. I have a flat file containing a date field in the format yyyymmdd and a time field in the format "hhmmss". What I want to do is take these two fields and merge them into a single datetime field in the format yyyy-mm-dd hh:mm:ss . I have tried using iconv and oconv, this is my best effort so far
Cats(Cats(Cats(Oconv(Iconv(DSLink3.call_date, "D"), "D-YMD"), " "), Oconv(Iconv(Cats(Cats(Cats(Cats(Left(DSLink3.call_time,2), ":"), Left(Right(DSLink3.call_time,4),2)), ":"), Right(DSLink3.call_time,2)), "MTS"), "MTS")),".000")
I have no problem outputting the string created to a flat file, but cannot convert it into a format that will be accepted by my database. Any hints or suggestions appreciated.
Regards,
Simon Vollett