String to Timestamp Conversion

kool78 · Post by **kool78** » Thu Jun 07, 2007 9:30 am

HI GURUS,

i have a job where i need to load a csv file into Oracle table..

one of the columns has the following data in the below format:

6/7/2007 19:00
10/11/2007 19:00
11/8/2007 19:00
12/6/2007 19:00
1/10/2008 19:00
2/7/2008 19:00
6/28/2007 19:00
7/12/2007 19:00
7/26/2007 19:00
5/31/2007 19:00
6/7/2007 19:00
6/14/2007 19:00
6/21/2007 19:00
6/28/2007 19:00
7/5/2007 19:00

i have to lad this string into oracle table whose column is a timestamp. i tried it in different ways..but couldnt get eh perfect solution for string to timestamp conversion..

can anyone throw some light or provide help on this.

appreciate any kind of information..
thanks

DSguru2B · Post by **DSguru2B** » Thu Jun 07, 2007 9:42 am

You need to work on two things:
1) Check the length of day and month, if its one then prefix it with a zero.
2) Append :00 as seconds to the end.

Then pass it to StringToTimestamp() with the mask of %mm/%dd/%yyyy %hh:%nn:%ss.

battaliou · Post by **battaliou** » Thu Jun 07, 2007 9:42 am

Ja, check this link out. There's plenty of info on timestamps.

viewtopic.php?t=95920&postdays=0&postor ... mp&start=0

kool78 · Post by **kool78** » Thu Jun 07, 2007 11:16 am

DSguru2B wrote:You need to work on two things:
1) Check the length of day and month, if its one then prefix it with a zero.
2) Append :00 as seconds to the end.

Then pass it to StringToTimestamp() with the mask of %mm/%dd/%yyyy %hh:%nn:%ss.

HI

I was able to convert the date part as

StringToDate(right('0':Field(DSLink41.WW_DATE,'/',1), 2):'/':right('0':Field(DSLink41.WW_DATE,'/',2), 2):'/':Field(DSLink41.WW_DATE,'/',3),"%mm/%dd/%yyyy")

but im not able to get the logice on how to get the timestamp part :

any help on the code ?

thanks

kool78 · Post by **kool78** » Thu Jun 07, 2007 12:03 pm

more details of how the column value looks like at the source side

06/07/2007 7:00:00 PM
10/11/2007 7:00:00 PM
11/08/2007 7:00:00 PM
12/06/2007 7:00:00 PM

unable to convert this to timestamp...

any help or code is appreciated.

thanks

DSguru2B · Post by **DSguru2B** » Thu Jun 07, 2007 12:05 pm

You need to check if day and month are single digit and only then prefix it with zero.
Specify stage variables to fix the day and month

Code: Select all

StgMonth-        If Len(Field(in.Date, "/", 1)) = 1 then "0":in.Date else in.Date
StgDay -         If Len(Field(StgMonth, "/", 2)) = 1 then Field(StgMonth, "/", 
                 1):"/0":StgMonth[4,20] else StgMonth

Now for the derivation. Use StgDay in your StringToTimestamp().

kool78 · Post by **kool78** » Thu Jun 07, 2007 12:13 pm

DSguru2B wrote:You need to check if day and month are single digit and only then prefix it with zero.
Specify stage variables to fix the day and month
Code: Select all
StgMonth-        If Len(Field(in.Date, "/", 1)) = 1 then "0":in.Date else in.Date
StgDay -         If Len(Field(StgMonth, "/", 2)) = 1 then Field(StgMonth, "/", 
                 1):"/0":StgMonth[4,20] else StgMonth
Now for the derivation. Use StgDay in your StringToTimestamp().

hey DSGURU2B,

thanks for your quick responses

when i se the data in csv file it appears as like this :
12/13/2007 19:00
12/20/2007 19:00
12/27/2007 19:00
1/3/2008 19:00
1/10/2008 19:00
1/17/2008 19:00
1/24/2008 19:00
1/31/2008 19:00
2/7/2008 19:00

and the same data when i read it thru a sequential file stage it appears as like this :

12/13/2007 7:00:00 PM
12/20/2007 7:00:00 PM
01/03/2008 7:00:00 PM

wht should i understand from this...im unable to convert this string format to timestamp.

any light on this

DSguru2B · Post by **DSguru2B** » Thu Jun 07, 2007 12:15 pm

How are you reading it as? Is it varchar? If thats the case then you dont need to do any month or day check. Just send it to StringToTimestamp() with the mask I gave you in my first post. It should work.

kool78 · Post by **kool78** » Thu Jun 07, 2007 1:47 pm

DSguru2B wrote:How are you reading it as? Is it varchar? If thats the case then you dont need to do any month or day check. Just send it to StringToTimestamp() with the mask I gave you in my first post. It should work.

hi i did that....

im reading it as varchar and then in transformer i did the stringtotimestamp conversion but its aborting,

since the format is in this way for ex: 06/12/2007 7:00:00 PM,,i think its not taking the time format in this..

any help on this

samsuf2002 · Post by **samsuf2002** » Thu Jun 07, 2007 2:04 pm

You are reading it as varchar in transformer but after using StringtoTimestamp you make sure that it should come out from transformer as timestamp SQL type.

kool78 · Post by **kool78** » Thu Jun 07, 2007 3:02 pm

samsuf2002 wrote:You are reading it as varchar in transformer but after using StringtoTimestamp you make sure that it should come out from transformer as timestamp SQL type.

yes i did that.....

the traget column metadata is timestamp:

but how do you reperesent the format for this kind of data

"6/23/2007 7:00:00 PM"

i mean what format you would privde?

samsuf2002 · Post by **samsuf2002** » Fri Jun 08, 2007 6:23 am

Format should be "%mm/%dd/%yyyy %hh:%mm:%ss"

DSguru2B · Post by **DSguru2B** » Fri Jun 08, 2007 7:24 am

A small correction. Its "%mm/%dd/%yyyy %hh:%nn:%ss (without any space in the time mask.)

samsuf2002 · Post by **samsuf2002** » Fri Jun 08, 2007 7:33 am

Ya thats right !

Thanks DSGuru