question

r_r avatar image
r_r asked

SQL Server Datetime issue

I am trying to convert utc time to different timezones. To achieve this I wrote a function that converts utc date time to the desired timezone. But the result is not accurate. The seconds value is missing. Here is a part of code create function f1 ( @input varchar(30)) return datetime as begin select @input = case when RIGHT(@input,3) = 'EST' then DATEADD(hour,-5,LEFT(@input, LEN(@input)-3)) else LEFT(@input, LEN(@input)-3) end return @input --Execution part -- exec f1 @input = '2017-12-05 12:43:29 EST' Expected result: 2017-12-05 07:43:29 Observed result: 2017-12-05 07:43:00.000 Help me to resolve the issue. Thanks in advance!
sql serverdatetimefunctionsudf
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

r_r avatar image r_r commented ·
The input time zone is not constant. It can be EST, CST, EDT,BST,etc. SO I wrote a function that checks for the input time zone and convert it to the desired datetime. Is there any other way to do this?
0 Likes 0 ·

1 Answer

·
WRBI avatar image
WRBI answered
Hi there, I've used SQL Fiddle to write the query slightly differently, but you should be able to convert it. Weirdly the T-SQL would run outside of case statement and give the second, but in the case statement it would drop the seconds! So I used an IIF statement, which worked. Here's the [SQL Fiddle][1] But here's the SQL, convert it into a function: DECLARE @INPUT VARCHAR(30) = '2017-12-05 12:43:29 EST' DECLARE @RETURNVALUE DATETIME SELECT @RETURNVALUE = IIF(RIGHT(@INPUT,3) = 'EST', DATEADD(HOUR,-5,CONVERT(DATETIME, LEFT(@INPUT, LEN(@INPUT)-3))), LEFT(@INPUT, LEN(@INPUT)-3) ) SELECT @RETURNVALUE IIF was available in SQL 2012 and above, I can't see a tag saying what you're using. [IIF MSDN][2] for your reference. Regards, Dave. [1]: http://sqlfiddle.com/#!18/9eecb/15821/0 [2]: https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.