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!
Are the built in functions of no use to you rather than creating your own? MSDN TODATETIMEOFFSET:
https://docs.microsoft.com/en-us/sql/t-sql/functions/todatetimeoffset-transact-sql AT TIME ZONE:
https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql DATETIMEOFFSET
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql
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
18 People are following this question.
