Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    84

    Unanswered: Daylight savings

    Anyone if SQL server has any built-in mechanism for handling Daylight savings?
    Mostly for figuring out time passed between two datetime values....

    Thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by afx2029
    Anyone if SQL server has any built-in mechanism for handling Daylight savings?
    No, since it's a regional thing anyway

    Mostly for figuring out time passed between two datetime values....
    Yes...DATEDIFF

    What are you trying to do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    84
    well, like you said I use DATEDIFF to get the amount of time passed between two dates....but (where I live daylight time shifts backwards at 2 (to 1 am) am last sunday of october, and forward at 2 am (to 3 am) on first sunday of april.

    basically I want to count time properly....

    DATEDIFF(Minute, '2004-04-04 1:30', '2004-04-04 3:30') gives 120 but in reality only 60 minutes passed between the first and second time.

  4. #4
    Join Date
    Oct 2003
    Posts
    84
    well, like you said I use DATEDIFF to get the amount of time passed between two dates....but (where I live daylight time shifts backwards at 2 (to 1 am) am last sunday of october, and forward at 2 am (to 3 am) on first sunday of april.

    basically I want to count time properly....

    DATEDIFF(Minute, '2004-04-04 1:30', '2004-04-04 3:30') gives 120 but in reality only 60 minutes passed between the first and second time.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to build a table that holds (perhaps by region) the dates and times that the switch occurs.

    There are some places in the states (by county level even) where the switch does not occur.


    Anyway. If the dates EXISTS in the range, then you need to handle it accordingly.

    Most likely with a CASE Statement
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If both of the DATETIME values are from the same locale as the server, you could use the GetUTCDate() to convert them both to UTC, then take the DateDiff() of the UTC DATETIME values. If the DATETIME values are from different locales, let me know how you do it!

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It say GetUTCDate() Requires 0 parameters...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    GetUTCDate() works just like GetDate(), but expresses the date/time returned as UTC (also known as Greenwich or Z time) instead of local time. If your app stores server based times in UTC (which is effectively mandated if you have servers in more than one timezone), then life is simple. If you have times stored based on local time, then things get to be really interesting!

    Oh yeah, I forgot to mention, there isn't any way to dependably convert a stored local time to Z time, although you can convert Z time to local time if you know which local time.

    -PatP
    Last edited by Pat Phelan; 12-20-04 at 22:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •