Page 1 of 1
extracting time from date
Posted: Tue Apr 25, 2006 6:13 am
by bhaskarjha
Hi,
I am trying to extract time part from a column whose data type is Date. For example my sample data is "1/1/1970 10:31:00 PM" and I want to take only "10:31:00 PM". My source table column is Date & target table column is also Date. I have tried it with DSTransform (TIMESTAMP.TO.DATE) but failed.
Thanks,
Bhaskar
Posted: Tue Apr 25, 2006 6:30 am
by ArndW
Just use the substring function, use RIGHT(In.ColumnName,11) since your string looks like it has a variable length date portion.
Posted: Tue Apr 25, 2006 6:31 am
by sb_akarmarkar
Hi,
Use substring function in transformer to extract time from Date like
example :substring("1/1/1970 10:31:00 PM",10,11)
Thanks,
Anupam
Posted: Tue Apr 25, 2006 6:59 am
by chulett
With that particular format, I doubt either portion will be of a fixed length. If that's the case, I'd use the FIELD function to split it at the first whitespace:
From memory. Otherwise, you could go with Arnd's solution.
Posted: Tue Apr 25, 2006 8:17 am
by ArndW
Craig - good thought, the function to use is
Field(In.ColumnName," ",2,1)
(although yours will work in this instance because there are no trailing spaces)
Posted: Tue Apr 25, 2006 8:53 am
by chulett
Specifically included the second '2' so that both pieces of the time will be extracted - the actual time and the 'AM/PM' part since it's not in 24 hour format. Assuming, that is, that there is actually a second space in there.
Posted: Tue Apr 25, 2006 9:41 am
by ArndW
You are, of course, correct. I'm just so used to European (or military) time that the AM/PM text completed zipped by me
. I think the FIELD function is a better way to go in any case, since it is more robust than using straightforward positional values.
Posted: Wed Apr 26, 2006 1:44 am
by bhaskarjha
Thanks to all of you for your valuable suggestion. My problem got resolved, I have used one routine to read the source & take only the time part.
Thanks,
Bhaskar