String to Timestamp Conversion
Moderators: chulett, rschirm, roy
String to Timestamp Conversion
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
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
"Attitude always and almost determines the altitude of your Life"
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.
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.
Last edited by DSguru2B on Thu Jun 07, 2007 9:43 am, edited 1 time in total.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Ja, check this link out. There's plenty of info on timestamps.
viewtopic.php?t=95920&postdays=0&postor ... mp&start=0
viewtopic.php?t=95920&postdays=0&postor ... mp&start=0
3NF: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. So help me Codd.
HIDSguru2B 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.
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
"Attitude always and almost determines the altitude of your Life"
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
Now for the derivation. Use StgDay in your StringToTimestamp().
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
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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 monthNow for the derivation. Use StgDay in your StringToTimestamp().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
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
"Attitude always and almost determines the altitude of your Life"
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
"Attitude always and almost determines the altitude of your Life"
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
yes i did that.....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.
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?
"Attitude always and almost determines the altitude of your Life"
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse