How to convert date values with timezone to dd-mm-yyyy forma

[bgs_vb]

Hi ,

I am getting data from source in a file in following format:
StartDate:22-10-2009
StartDate:22/10/2009
StartDate:21102009
StartDate:Thu Jan 21 14:20:00 EST 2008
I need to convert these different date formats into one common date format i.e dd-mm-yyyy using datastage and load to target oracle database .I tried using stringtodate function but its not working .

Please help me in convert different date formats of source to common date format.

[nani0907]

you should check step by step.First remove all the -,'/' and bring it to one format i.e DDMMYYYY,then convert the also DMMYYYY using stage varible .After that do a date validation to check whether it is a valid date or not.

[bgs_vb]

Hi,
Thanks for your response ,

The problem that I am facing is with timezone i.e EST .As source can have date in any of the time zone fmt i.e(PST,EST,CST... etc) .. I can replace '/','-' but to replace timezone I need to extract it first .And I din't find any function in datastage to extract timezones .

[chulett]

What do you actually need to do with the timezone information? Convert it to a "standard" timezone? Ignore it? For the latter, you could use the Field function to get just the parts that you want. For example:

Code: Select all

Field("Thu Jan 21 14:20:00 EST 2008"," ",2,3)

Gets the three "fields" from a space delimited string starting from the second field, so would return "Jan 21 14:20:00". The day name adds no value here so we can ignore it, I would think. And then:

Code: Select all

Field("Thu Jan 21 14:20:00 EST 2008"," ",6,1)

Would return the sixth field, the year. Those two pieces could then be put together to get you the timestamp to convert. Obviously you'd still need to split the time out from the month/day, I'll leave that as an excercise for you, a simple change to use three Field() calls rather than two.

[ray.wurlod]

Is a solution required for any possible time zone in the world? For example Indian Standard Time will require you to change minutes as well as hours.

Some conversions - even within the USA - will require the day to be changed and then, as a possible flow-on, the month and maybe even year.