0

I've been trying to get this work over two days and still stuck. Any assistance or tips would be greatly appreciated.

Creating a function for the date conversion:

    CREATE FUNCTION LocalDateFromUTCTime
(
    @UTCDateTime datetime
)
RETURNS datetime
BEGIN
    DECLARE @diff int;
    SET @diff = datediff(hh,GetUTCDate(), GetDate());
    RETURN DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, @diff, @UTCDateTime)),0);
END

Then select SQL statement I'm trying to use with the function above is below:

SELECT 
        o.Number AS 'Order#',
        o.[guid] as 'guid',
        dbo.LocalDateFromUTCTime(o.settlementdate) as 'closing date'

FROM pf.order o

doing this displays the settlement date with no timestamp.

2015-07-15 00:00:00.000

How could I change this to show the correct Local time?

Thanks

user3571153
  • 129
  • 1
  • 9
  • What is the question here? You haven't included the full code of the procedure, the problem you are having or what your desired result is. – iamdave Nov 17 '16 at 16:26
  • I'm trying to convert UTC to Local Time. Sorry. I just do not know how to change the function for me to display the settlementdate as Local time and not UTC time. – user3571153 Nov 17 '16 at 16:29
  • That function looks like you pass in a datetime value and then the timezone you want to convert it to. Without seeing the code in the function (Which you have not provided here) we are not going to be able to help you. – iamdave Nov 17 '16 at 16:43
  • Sorry, the code is included now. – user3571153 Nov 17 '16 at 16:48
  • Please let me know what I can do with the function to show the local time. – user3571153 Nov 17 '16 at 16:51

1 Answers1

1

Your date conversion is specifically returning the difference in days. The dateadd is adding the number of days between date 0 (which is actually 01-01-1900) and your UTC date onto a different date 0. This means that the hours and minutes are completely ignored.

If you want to include the hours and minutes you just need to drop the dateadd(...datediff( part:

RETURN DATEADD(hh, @diff, @UTCDateTime)

iamdave
  • 12,023
  • 3
  • 24
  • 53