DSXchange

Hi All,

From source I am getting date field value in IST time zone, where as in target I have to load the date in PST time zone and calculating the difference between start time and end time. For example the difference between these two time zone is around 13 and half hours, so if the date is 11.00 pm in IST then it should be 12.30 am in PST. Any help how to convert time zone and calculate the values.

Thanks in advance
Rajeev

Welcome aboard. You need to detect time zone, because it is not a fact that the difference is always the same; when the USA switches to or from summer time the difference changes. After that it's a simple matter of decomposing your timestamp into its components and performing appropriate arithmetic on the minute, hour and date portions.

Hi Ray ...

What exactly do you mean by detect.
From the timestamp , would we have to conclude if it is Daylight savings or not. and then apply the time difference accordingly ?

Unlikely that you can detect it from the timestamp; you probably need an extra column in the data or to read the TZ environment variable. The latter will yield the time zone of the DataStage server.

[/quote]to read the TZ environment variable. The latter will yield the time zone of the DataStage server.

Ok. I do not get an extra field in my input data file so i would have to use the second method. My issue here is we have to store the date in the UTC format in the database.

The NLS - Locale used is ES_SPANISH . How can i achieve this ?

I thought we have to convert the EST to UTC format...is it right or is there any simpler way of doing it ?