Find next nearest Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Find next nearest Time

Author

Topic

Cass
Starting Member

9 Posts

Posted - 2009-06-22 : 09:32:49

Hi!
I have table where is inserted times (time data type) when my application must do some kind of work.
How can i find the next nearest time and set this time activated?

Timers table looks like:
09:00:00.0000000 | 0
12:00:00.0000000 | 0
19:00:00.0000000 | 0
22:00:00.0000000 | 0

If current Time is 14:15:30.0000000
then set next time to 19:00:00
09:00:00.0000000 | 0
12:00:00.0000000 | 0
19:00:00.0000000 | 1
22:00:00.0000000 | 0

In MS Access work this code but how in Transact-SQL?


dbs.Execute "UPDATE Timers SET Timers.Active = 1
     WHERE ((([RunningTimes]-Time()) In
     (SELECT Min(IIf([RunningTimes]-Time()<0,99999999 [RunningTimes]-Time())) FROM Timers;)));"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 09:39:06

[code]UPDATE f
SET Active = 1
FROM (
SELECT TOP 1
Active
FROM Timers
WHERE RunningTimes >= DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE())
ORDER BY RunningTimes
) AS f[/code]EDIT: Formatting

E 12°55'05.63"
N 56°04'39.26"

Cass
Starting Member

9 Posts

Posted - 2009-06-22 : 09:46:13

Thank you :)

I'm using this


DECLARE @Tm3 time = SYSDATETIME()
UPDATE f
SET Active = 1
FROM (
SELECT TOP 1 Active 
FROM Timers
WHERE RunningTime >= @Tm3
ORDER BY RunningTime 
) AS f

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 09:53:17

The DECLARE part without precision makes the @tm3 variable also hold microseconds.
If you are interested in seconds only, try declaring the @tm3 variable as TIME(0).

UPDATE  f
SET     Active = 1
FROM    (
            SELECT      TOP 1
                        Active
            FROM        Timers
            WHERE       RunningTimes >= CAST(GETDATE() AS TIME(0))
            ORDER BY    RunningTimes
        ) AS f

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources