I’m having an issue on how to add the Offset to the EventDateTime. Is there a way to do this? I would convert it to a decimal number but cannot because 5:30 is not 5.3 hours, it’s 5 hours and 30 minutes.
The data is shown below with the expected output. Please help if possible.
Sample Data:
+-----------+-----------+---------------------+-------------------------+--------+ | EventDate | EventTime | EventDateTime | TimeZone | Offset | +-----------+-----------+---------------------+-------------------------+--------+ | 10/1/2020 | 5:00 PM | 2020-10-01 5:00 PM | UTC +5:30 IST New Delhi | +5:30 | | 10/1/2020 | 8:00 PM | 2020-10-01 8:00 PM | UTC +5:30 IST New Delhi | +5:30 | | 10/2/2020 | 10:00 AM | 2020-10-02 10:00 AM | UTC -4 EDT New York | -4 | | 10/2/2020 | 10:00 AM | 2020-10-02 10:00 AM | UTC -7 PDT Los Angeles | -7 | | 10/2/2020 | 2:00 PM | 2020-10-02 2:00 PM | UTC +1 BST London | 1 | | 10/2/2020 | 3:00 PM | 2020-10-02 3:00 PM | UTC -7 PDT Los Angeles | -7 | | 10/2/2020 | 8:00 AM | 2020-10-02 8:00 AM | UTC -7 PDT Los Angeles | -7 | | 10/5/2020 | 10:00 AM | 2020-10-05 10:00 AM | UTC +5:30 IST New Delhi | +5:30 | | 10/5/2020 | 10:00 AM | 2020-10-05 10:00 AM | UTC -5 CDT Chicago | -5 | +-----------+-----------+---------------------+-------------------------+--------+
Expected Outcome:
+-----------+-----------+---------------------+-------------------------+--------+---------------------+ | EventDate | EventTime | EventDateTime | TimeZone | Offset | UTCDateTime | +-----------+-----------+---------------------+-------------------------+--------+---------------------+ | 10/1/2020 | 5:00 PM | 2020-10-01 5:00 PM | UTC +5:30 IST New Delhi | +5:30 | 2020-10-01 10:30 PM | | 10/1/2020 | 8:00 PM | 2020-10-01 8:00 PM | UTC +5:30 IST New Delhi | +5:30 | 2020-10-02 1:30 AM | | 10/2/2020 | 10:00 AM | 2020-10-02 10:00 AM | UTC -4 EDT New York | -4 | 2020-10-02 6:00 AM | | 10/2/2020 | 10:00 AM | 2020-10-02 10:00 AM | UTC -7 PDT Los Angeles | -7 | 2020-10-02 3:00 AM | | 10/2/2020 | 2:00 PM | 2020-10-02 2:00 PM | UTC +1 BST London | 1 | 2020-10-02 3:00 PM | | 10/2/2020 | 3:00 PM | 2020-10-02 3:00 PM | UTC -7 PDT Los Angeles | -7 | 2020-10-02 8:00 AM | | 10/2/2020 | 8:00 AM | 2020-10-02 8:00 AM | UTC -7 PDT Los Angeles | -7 | 2020-10-02 1:00 AM | | 10/5/2020 | 10:00 AM | 2020-10-05 10:00 AM | UTC +5:30 IST New Delhi | +5:30 | 2020-10-05 3:30 PM | | 10/5/2020 | 10:00 AM | 2020-10-05 10:00 AM | UTC -5 CDT Chicago | -5 | 2020-10-05 5:00 AM | +-----------+-----------+---------------------+-------------------------+--------+---------------------+
What I have done so far:
Advertisement
Answer
Not exactly pretty, but it works…
DECLARE @eventDateTime VARCHAR(50), @offset varchar(10) SET @eventDateTime = '2020-09-30 5:00AM' SET @offset = '-5:30' SELECT CASE WHEN @offset LIKE '-%' THEN CONVERT(DATETIME,@eventDateTime) - REPLACE(@offset,'-','') ELSE CONVERT(DATETIME,@eventDateTime) + REPLACE(@offset,'+','') END