SQL Server - Calculate # seconds from now until :30 past the hour

Asked By Karch on 03-Aug-07 07:23 PM
How best to calculate the number of seconds from now until 30 minutes past
the hour?




Hugo Kornelis replied on 04-Aug-07 07:46 AM
Hi Karch,

SELECT DATEDIFF(second,
CURRENT_TIMESTAMP,
DATEADD(hour,
DATEDIFF(hour,
'2000-01-01T00:00:00',
CURRENT_TIMESTAMP),
'2000-01-01T01:00:30'));

Your specification is a bit unclear on what to return if the current
time is between 0 and 30 seconds past the hour. The code above will
return values of 3600 up to 3630 for those times. If you need them to be
from 0 to 30, you could either add some very complicated date/time
logic, or (much easier) simply use the modulo operator as follows:

SELECT DATEDIFF(second,
CURRENT_TIMESTAMP,
DATEADD(hour,
DATEDIFF(hour,
'2000-01-01T00:00:00',
CURRENT_TIMESTAMP),
'2000-01-01T01:00:30')) % 3600;


--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Karch replied on 04-Aug-07 09:45 PM
This looks like it returns the number of seconds to the top of the hour.

The more I thought about it, what I was actually looking for was the query
to return the number of seconds until EITHER the top of the hour xx:00 or
the half of the hour xx:30 (looking forward in time, not backward). I am
using this to schedule a job beginning on the next half hour and then run
every 30 minutes afterward.
Dan Guzman replied on 05-Aug-07 09:47 AM
SELECT
1800 - DATEDIFF(
second,
DATEADD(day, 0, DATEDIFF(day, '19000101', CURRENT_TIMESTAMP)),
CURRENT_TIMESTAMP) % 1800
AS SecondsTillNextRun

To calculate the time of the next run:

SELECT
DATEADD(
minute,
(1 + DATEDIFF(
minute,
DATEADD(day, 0, DATEDIFF(day, '19000101', CURRENT_TIMESTAMP)),
CURRENT_TIMESTAMP) / 30) * 30,
DATEADD(day, 0, DATEDIFF(day, '19000101', CURRENT_TIMESTAMP))
) AS NextRunTime


--
Hope this helps.

Dan Guzman
SQL Server MVP
Hugo Kornelis replied on 05-Aug-07 04:28 PM
Hi Karch,

My fault - I misread your post and thought you wanted the number of
seconds until 30 SECONDS (rather than minutes) past the hour.


So if I understand correctly, you want the number of seconds until the
next full or half hour?

Here's a slightly unconventional approach:

SELECT 1800 - DATEDIFF(second,
'2007-01-01T00:00:00',
CURRENT_TIMESTAMP) % 1800;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Karch replied on 05-Aug-07 06:54 PM
Thanks to both of you - working great!
Karch replied on 10-Aug-07 09:04 PM
What about starting on the next 30 minutes past the hour and then running
every 60 minutes after that?
Karch replied on 10-Aug-07 10:37 PM
I will answer my own question:

SELECT 3600 - DATEDIFF(

second,DATEADD(minute, 30, DATEADD(day, 0, DATEDIFF(day, '19000101',
CURRENT_TIMESTAMP))),

CURRENT_TIMESTAMP) % 3600
Dan Guzman replied on 11-Aug-07 08:11 AM
Thanks for posting your solution.  Maybe that will help someone later who
stumbles on this thread.

--
Hope this helps.

Dan Guzman
SQL Server MVP