Post reply

Converting UTC time in GETDATE clause

  • January 5, 2012 at 10:09 am

    #249139

    I am getting UTC time from the column and want the actal time, where in the clause would I tell it timestamp -600 hours?

    (UTCOccurrenceDateTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

    MCSE SQL Server 2012\2014\2016

  • January 5, 2012 at 10:56 am

    #1429214

    there's another function for the UTC date you cvan use: GETUTCDATE()

    select (UTCOccurrenceDateTime >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0))

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 5, 2012 at 11:02 am

    #1429219

    Do you need to take DST (Daylight saving time) into account ? ( search SSC for solutions )

    Just to remind some pitfalls ...:Whistling:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 5, 2012 at 11:36 am

    #1429254

    Thanks Lowell but it still came back with UTC time, Basically I am trying to convert the time and then count Column1, Column3, and Column4, and order it by Column1 and Column4, but no luck, heres a basic query without breakin CO rules,

    SELECT Column1, COUNT(*) AS Column2, Column3, Column4

    FROM tblname

    WHERE (Column1 >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0))

    AND (Column4 LIKE 'Disk %')

    AND (Column4 NOT LIKE 'Disk C%') AND (Column for percentage>= 95.00)

    GROUP BY Column1, Column4, Column3

    HAVING (COUNT(*) < 250000)

    ORDER BY Column3, Column1

    It counts until I put the time in them I get one for each one on Column3 - lost...haha

    MCSE SQL Server 2012\2014\2016

  • January 5, 2012 at 11:36 am

    #1429257

    I have thought of that ALZDBA, ah can't wait to spring forward...

    MCSE SQL Server 2012\2014\2016

  • January 5, 2012 at 11:42 am

    #1429262

    Lowell,

    Here a query that returns count without the date and time convertion

    SELECT Column1, COUNT(*) AS [Count], Column3, Column4

    FROM Alerts

    WHERE (Column1 LIKE 'Disk %') AND (Column1 NOT LIKE 'Disk C%')

    AND (Column3 >= 95.00)

    GROUP BY Column1, Column3, Column4

    HAVING (COUNT(*) < 250000)

    ORDER BY Column4

    Weird...

    MCSE SQL Server 2012\2014\2016

  • January 5, 2012 at 12:30 pm

    #1429292

    oh and the timedatestamp is

    YYYY-MM-DD HH:MM:SS:MMM

    Someone help me please, so confused and it so easy....

    MCSE SQL Server 2012\2014\2016

  • January 5, 2012 at 1:33 pm

    #1429337

    It's hard to help with the code you have posted. In your first example Column1 looks like a date column. In your second example it looks like you are comparing Column1 to text values. I'm not real sure what the structure of the table you are querying looks like.

  • January 5, 2012 at 2:48 pm

    #1429377

    Okay here is a simpler query that i need Central time and date but it is in UTC format;

    select ServerName, Heading, UTCOccurrenceDateTime

    from dbo.Alerts where Heading like '%fail%'

    AND (UTCOccurrenceDateTime >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0))

    order by UTCOccurrenceDateTime

    MCSE SQL Server 2012\2014\2016

  • January 5, 2012 at 2:59 pm

    #1429382

    If I am understanding this the way I should be then should you just need something like this

    DATEADD(hh, -6, UTCOccurrenceDateTime) >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0)

    after the AND in your WHERE statement?

    Except you still will have issues with DST as ALZDBA suggested earlier.

  • January 5, 2012 at 3:14 pm

    #1429392

    Yeah that did it, now back to the other query and trying to get it to count by Occurance, ServerName.

    Thanks so much!

    lk

    MCSE SQL Server 2012\2014\2016

  • January 5, 2012 at 3:21 pm

    #1429395

    No problem, just be careful if daylight savings time is involved here. 6 hours difference from UTC on a time in January may not be the same thing you want on a time in July. Not sure if this is your case or not, just something to be aware of.

  • January 5, 2012 at 3:53 pm

    #1429406

    Don't put a function on your tables column ! (it ruins index usage !)

    do the function on the foldable calculated formula on getdate !!

    and consider putting an index on that datetime column, if that helps your applications/datasystem

    edited:

    I attached a function I used to determine the DST correction.

    Keep in mind this function is for the Brussels time zone.

    ( I'm sorry for the comments in Dutch, but I think you'll get the message )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 6, 2012 at 2:59 pm

    #1429889

    Thanks guys I got all of figured out, now just putting it into SSRS, whuho!! 🙂

    MCSE SQL Server 2012\2014\2016

Viewing 14 posts - 1 through 13 (of 13 total)

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