CODEWITHSUNDEEP
sqlsql-servert-sql
Tom
Tom

Reputation: 2230

TSQL Update DATETIME Minutes and Seconds

I've been staring at this for quite a while and can't figure out how to go about doing it. I don't really have any code to post because I haven't even gotten close to figuring it out yet and everything I come up with just doesn't make any sense.

Anyways, we have an application that collects data every 5 minutes based on the max(readtime) in the table (but also will only show real values if the time is a multiple of 5 and is at :00.000 seconds.) For the most part, this is fine, but sometimes the data will get out of sync and cause a bunch of 0 values in our table.

Currently, we've just been going in, grabbing the latest readtime, and hardcoding the new date that we'd like to use. I'm looking for a script I can use to automate this. For the purposes of this question, we can use GETDATE().

DECLARE @readtime DATETIME = GETDATE()  --2011-08-09 08:51:19.237
             -- I want it to look like    2011-08-09 08:50:00.000

Upvotes: 9

Views: 2562

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

Round down to nearest 5 minute.

DECLARE @readtime DATETIME = 
    DATEADD(minute, 5*FLOOR(DATEDIFF(minute, 0, GETDATE())/5.0), 0)

Upvotes: 3

JNK
JNK

Reputation: 65217

Try this.

It basically checks minutes to be divisible by 5, seconds by 6 and ms by 1000, and if they aren't it subtracts the remainder from each:

DECLARE @dt datetime = '2011-08-09 08:51:19.237'

SELECT DATEADD(
                MILLISECOND, 
                -(DATEPART(MILLISECOND, @dt) % 1000), 
                DATEADD(
                        SECOND, 
                        -(DATEPART(second, @dt) % 60), 
                        DATEADD(
                                minute, 
                                -(DATEPART(minute, @dt) % 5), @dt )
                        )
                )

Output:

2011-08-09 08:50:00.000

Upvotes: 4

Related Questions