Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amien
Specialist
Specialist

2011-07-18T23:22:34.166-07:00

How do i get this date to my local datetime?

i tried ConvertToLocalTime (both Mountain Time (US & Canada) and Amsterdam). but doesn't work?

how can i convert this to datetime in Amsterdam? GMT+1

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I think there is no standard method to read this Timestamp with given timezone in and then convert it to a different timezone. The Timestamp stated in your header is already Mountain Time, right?

I would suggest to read it in as GMT, then you might convert it to any timezone you like:

timestamp(date#(mid('2011-07-18T23:22:34.166-07:00',1,10),'YYYY-MM-DD')

   +time#(mid('2011-07-18T23:22:34.166-07:00',12,12),'hh:mm:ss.fff')

   +if(mid('2011-07-18T23:22:34.166-07:00',24,1)='+',-1,+1)

   *interval#(mid('2011-07-18T23:22:34.166-07:00',25,5),'hh:mm')) as GMTTimeStamp

Then you can use

ConvertToLocalTime(GMTTimeStamp,'Amsterdam')

to convert your date time to Amsterdam Time (check out also the 3. parameter, to ignore daylight saving).

Hope this helps,

Stefan

View solution in original post

3 Replies
swuehl
MVP
MVP

I think there is no standard method to read this Timestamp with given timezone in and then convert it to a different timezone. The Timestamp stated in your header is already Mountain Time, right?

I would suggest to read it in as GMT, then you might convert it to any timezone you like:

timestamp(date#(mid('2011-07-18T23:22:34.166-07:00',1,10),'YYYY-MM-DD')

   +time#(mid('2011-07-18T23:22:34.166-07:00',12,12),'hh:mm:ss.fff')

   +if(mid('2011-07-18T23:22:34.166-07:00',24,1)='+',-1,+1)

   *interval#(mid('2011-07-18T23:22:34.166-07:00',25,5),'hh:mm')) as GMTTimeStamp

Then you can use

ConvertToLocalTime(GMTTimeStamp,'Amsterdam')

to convert your date time to Amsterdam Time (check out also the 3. parameter, to ignore daylight saving).

Hope this helps,

Stefan

amien
Specialist
Specialist
Author

Thanks for your reply swuehl..

changed only this:

='+',-1,+1)

to

='+',+1,-1)

swuehl
MVP
MVP

Are you sure about your change?

I think we need to calc the GMT time and in your example, 2011-07-18T23:22:34.166-07:00 means that the given local time is 7 hours less than or behind GMT. So we actually need to add 7 hours to get to GMT, right? I think you will also see that you need to chose the sign like in my example if you then try to use ConvertToLocalTime with e.g. Arizona, which should result again in 2011-07-18 23:22:34.166, right?

Regards,

Stefan