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!
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] 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] for your reference. Regards, Dave. :