CA Service Management

Expand all | Collapse all

Daylight Summer/Saving Time in SQL

  • 1.  Daylight Summer/Saving Time in SQL

    Posted 03-23-2015 09:47 AM

    I am building a SQL query but I have problems with Daylight Summer Time. I would like to know if someone knows which is the correct formula to handle DST in SQL.



  • 2.  Re: Daylight Summer/Saving Time in SQL

     
    Posted 03-23-2015 11:13 PM

    My first question would be why do you want to include DST?  For KPI reporting purposes (e.g. number of tickets opened / closed this month) it’s not going to seriously affect the precision of a KPI if for some months of the year the month starts and ends an hour early.

     

    But if you really, really need to do this, you will need to set up a reference table of DST starts and ends and then write an SQL function that you can use to return the DST offset (0 or 1) for any given date.  Then you can use the offset in formulas like “dateadd(second, SDMdate + offset6060, ’01-01-1970 08:00:00’)” – the ’01-01-1970 08:00:00’ reflects my time zone’s offset from UTC – yours will be different.  Be prepared to maintain your reference table over the coming years.  There is (at least according to my limited research on the subject) no switch you can turn on in SQL to do it.  Thankfully I live in a state that only ever tried DST for one year, and then voted not to bother with it!

     

    Regards,

    James



  • 3.  Re: Daylight Summer/Saving Time in SQL

    Posted 03-24-2015 01:05 PM

    I am trying avoiding using DST time but I need it. I am comparing the time between initial, transfer and resolved in the activity log for a report. I know in BO universe has the right conversion but I don't have access to the schema casd to see the objects in SQL. I believe that casd is using a function for that.