Post reply

Convert UTC time to local time

  • February 26, 2018 at 7:57 am

    #343733

    I have table with column of datetime, for example : 2017-03-27 22:30:23.000

    I want to convert it to local time (in the above example I need to add to my local time which is GMT+2)

    can I do it with SWITCHOFFSET? then how?
    when i try to do :

    SELECT top 10 SWITCHOFFSET(table1.utc_time,'+02:00') FROM table1
    where utc_time is not null

    i get :
       I get an error : (the column is of datatype datetime)

    Argument data type datetime is invalid for argument 1 of switchoffset function.
    what do i do wrong?

  • February 26, 2018 at 8:14 am

    #1981102

    Have you tried AT TIME ZONE instead?

    John

  • February 26, 2018 at 8:37 am

    #1981106

    wanttolearn1 - Monday, February 26, 2018 7:57 AM

    I have table with column of datetime, for example : 2017-03-27 22:30:23.000

    I want to convert it to local time (in the above example I need to add to my local time which is GMT+2)

    can I do it with SWITCHOFFSET? then how?
    when i try to do :

    SELECT top 10 SWITCHOFFSET(table1.utc_time,'+02:00') FROM table1
    where utc_time is not null

    i get :
       I get an error : (the column is of datatype datetime)

    Argument data type datetime is invalid for argument 1 of switchoffset function.
    what do i do wrong?

    What version of SQL Server are you running?  I'm running on SQL 2012 and I am not getting that error. I also checked the documentation and datetime data can be implicitly converted to datetimeoffset, so you should not be getting that error at all.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • February 26, 2018 at 10:15 am

    #1981140

    wanttolearn1 - Monday, February 26, 2018 7:57 AM

    I have table with column of datetime, for example : 2017-03-27 22:30:23.000

    I want to convert it to local time (in the above example I need to add to my local time which is GMT+2)

    can I do it with SWITCHOFFSET? then how?
    when i try to do :

    SELECT top 10 SWITCHOFFSET(table1.utc_time,'+02:00') FROM table1
    where utc_time is not null

    i get :
       I get an error : (the column is of datatype datetime)

    Argument data type datetime is invalid for argument 1 of switchoffset function.
    what do i do wrong?

    You need to use a conversion to a datetimeoffset data type
    😎

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply