UTC OFFSET time adjustment --

1

Hi I have the following Data and want to convert the datetime to local zone 'iv the timestamp(foreign ) and UTC Infor as under

callEventStartTimeStamp utcTimeOffset
2017-04-10 09:19:06.000 +0400
2017-04-10 09:31:59.000 +0400
2017-04-10 09:19:39.000 +0400
2017-04-11 07:43:51.000 +0400
2017-04-11 07:51:29.000 +0400
2

What data type would utcTimeOffset. And what is your plan for this column, what are the requirements?

1 Like
3

Dear yosiasz ,the basic issue to convert the provided other time Zone with UTC (+0400) to Local Time Zone (+0500)

4

This is just one of the reasons why you should be using the correct data type - which in this case would be DATETIMEOFFSET. By storing the time zone offset separately - and in a non-valid format, you now need to combine the values to get the results.

Before we can determine how to combine this data - we need to know what you expect. For example, is this date '2017-04-10 09:19:06.000' the date/time before or after applying the UTC offset? If we apply that offset we would get '2017-04-10 13:19:06.000 +04:00' and so the 'local' time would be '2017-04-10 14:19:06.0000000 +05:00'

Or - should this be '2017-04-10 09:19:06.000 +04:00' and therefore local time would be '2017-04-10 10:19:06.000 +05:00'.

Run this and let us know the actual time zone names for each zone:

 Select *
   From sys.time_zone_info tzi
  Where tzi.current_utc_offset In ('+04:00', '+05:00');

We can then - probably - use AT TIME ZONE to convert to the appropriate values as needed.

2 Likes