extracting time from date

[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

[ArndW]

Just use the substring function, use RIGHT(In.ColumnName,11) since your string looks like it has a variable length date portion.

[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

[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:

Code: Select all

Field(In.ColumnName," ",2,2)

From memory. Otherwise, you could go with Arnd's solution.

[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)

[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.

[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.

[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