locked
Date format in Report Builder when using UTC server RRS feed

  • Question

  • Dear all,

    I am building reports based on Report Builder and SQL server 2014.
    Our report data are hosted on azure sql database.

    Date time on azure are stored as UTC.

    When I run my report locally, how can I format correctly the date/time format on my report in order that if I am in French, I should have the date/time set to UTC + French offset

    Any idea how can I do this ?

    regards

    Wednesday, October 14, 2015 12:54 PM

Answers

  • Hi wakefun,

    In your scenario, you can convert the date field in dataset query to corresponding date based on the current region like below, then you can add those date field to the report.

    declare @dt datetime ='20150101 00:00:00'
    select dateadd(hour,datediff(hour,GETUTCDATE(),getdate()),@dt) 

    Or else you need to use the TimeZoneInfo.ConvertTimeBySystemTimeZoneId method in report expression as Milan suggested.

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Thursday, October 15, 2015 8:43 AM

All replies

  • Hi wakefun,

    You can achieve that in the below either at query level or SSRS expression 

    In the query Editor, use the below query

    SELECT GETUTCDATE() as UTCDate

    In SSRS check the below link

    http://stackoverflow.com/questions/2969689/working-with-time-zones-in-ssrs


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Wednesday, October 14, 2015 5:16 PM
  • I have check your link and I cannot not use the function as they discribe because I have a lot of row and will consume a lot of time to parse all rows to correct date.

    Any simple embebded conversion in report builder ?

    regards

    Thursday, October 15, 2015 7:14 AM
  • Hi wakefun,

    In your scenario, you can convert the date field in dataset query to corresponding date based on the current region like below, then you can add those date field to the report.

    declare @dt datetime ='20150101 00:00:00'
    select dateadd(hour,datediff(hour,GETUTCDATE(),getdate()),@dt) 

    Or else you need to use the TimeZoneInfo.ConvertTimeBySystemTimeZoneId method in report expression as Milan suggested.

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Thursday, October 15, 2015 8:43 AM