GETUTCDATE but specify custom time

Question

I'm trying to write an automation code for which I want to use the current date but the time must be specific.

SELECT
   col_name
FROM
   table
WHERE
   CONVERT(DATE, start_datetime) = CONVERT(date, GETUTCDATE())

How do I mention specific time?

If your timezone is GMT and you want to convert to UTC....I don't know how much work you have ahead of you — Brad, Aug 17 '16 at 17:26
Using equality on time is usually a bad idea. Convert time to a string and compare the strings. — rheitzman, Aug 17 '16 at 17:29
Any differences between GMT and UTC are negligible for this type of work. http://stackoverflow.com/questions/14986043/is-gmt-same-as-utc — Nick, Aug 17 '16 at 17:30
How specific must the time be? If you are looking to match on DATE, which you have already done, then just match on an hour (for instance) you can make use of the `DATEPART` function. — Nick, Aug 17 '16 at 17:31
@rheitzman I don't think converting to string is the way to go. Maybe dropping the milliseconds but maybe OP does want to do exactly that kind of equality testing. — Brad, Aug 17 '16 at 17:34
@Nicarus Yes, I wanted to match on date which is working, I want to get values only after certain time. Looking at the datepart function now. Thanks — Mailer456, Aug 17 '16 at 17:44

score 1 · Accepted Answer · answered Aug 17 '16 at 17:50

wow took me a minute I wasn't understanding your question. but I think you are asking how do you specify a time today and compare that to your start_datetime value to see if they are at the same time.

The question will come down to how accurate do you want to be. e.g. at a specific hour? within x # of minutes? Seconds....????

And there are tons of ways of answering this.

The first question is what timezone is your start_datetime stored in? Because you likely do not want to use UTC Date if your start_date is not also in UTC! If not utc what timezone is your server set to, would GETDATE() and start_datetime be in the same zone?

Anytime Today in a specific hour.

SELECT
   col_name
FROM
   table
WHERE
   CONVERT(DATE, start_datetime) = CONVERT(date, GETUTCDATE())
   AND HOUR(start_datetime) = 5

Anytime Today at specific hour and minute

SELECT
   col_name
FROM
   table
WHERE
   CONVERT(DATE, start_datetime) = CONVERT(date, GETUTCDATE())
   AND DATEPRT(HOUR,start_datetime) = 5
   AND DATEPART(MINUTE,start_datetime) = 15

OR

SELECT
   col_name
FROM
   table
WHERE
   start_datetime = CAST(CAST(GETUTCDATE() AS DATE) AS DATETIME) + CAST('05:15' AS DATETIME)

OR

SELECT
   col_name
FROM
   table
WHERE
   start_datetime = CONVERT(DATETIME,(CONVERT(VARCHAR(10),GETUTCDATE(),120) + ' 19:15'))

For seconds you can use basically the same technique as the hour/minute only add an either seconds to the string you are converting or

For after a specific time just switch the = to >

glad it helps out. Please accept my if it worked for you, here is an article about how/why/when if you need it: http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work?newreg=90b0e660d05f48ffaf48a419eaf51dcf — Matt, Aug 18 '16 at 15:24

GETUTCDATE but specify custom time

1 Answers1