UTC millisecond Cast DateAdd Hours error

How should I write this to get anything older than 3 hours?



WHERE TimeStamp >  CAST(DATEDIFF(SECOND, '19700101', DATEADD(hh, -3, CAST(GETDATE() AS date))) AS bigint) * 1000

Open in new window

CAMPzxzxDeathzxzxAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
This is somewhat confusing.  My very first comment had the code to handle that.  Not sure what else you're looking for:

WHERE TimeStamp >=  CAST(DATEDIFF(SECOND, '19700101', DATEADD(hh, -3, GETDATE())) AS bigint) * 1000

(The "CAST(DATEDIFF(SECOND, '19700101', DATEADD(hh, -3, GETDATE())) AS bigint) * 1000" is exactly what I first posted to address your issue.)

If you need UTC instead, change GETDATE() to get UTC time, but the rest of it can stay the same.
0
 
Scott PletcherSenior DBACommented:
Don't cast as date, because date doesn't have hours:

CAST(DATEDIFF(SECOND, '19700101', DATEADD(hh, -3, GETDATE())) AS bigint) * 1000
0
 
CAMPzxzxDeathzxzxAuthor Commented:
I don't understand?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CAMPzxzxDeathzxzxAuthor Commented:
How should I write this?
0
 
CAMPzxzxDeathzxzxAuthor Commented:
WHERE [TimeStamp] < DATEADD(ms, -10800000, GETUTCDATE())
0
 
Scott PletcherSenior DBACommented:
Now I don't understand.  Is TimeStamp a true datetime column or it elapsed ms since 1970-01-01 (unix style date)?

If it's a datetime, then, yes, I would think your last style of code would be right.
0
 
CAMPzxzxDeathzxzxAuthor Commented:
The TimeStamp is UTC milliseconds.  I do not have this working yet.  The error is "Arithmetic overflow error converting expression to data type datetime"
0
 
CAMPzxzxDeathzxzxAuthor Commented:
This is the first time I've worked with UTC and you can tell I just don't get it.  Yes - Is TimeStamp a true datetime column or it elapsed ms since 1970-01-01 (unix style date)?

Please help - this is the last piece to put this to bed.
0
 
CAMPzxzxDeathzxzxAuthor Commented:
elapsed ms since 1970-01-01 (unix style date)
0
 
CAMPzxzxDeathzxzxAuthor Commented:
Please
0
 
CAMPzxzxDeathzxzxAuthor Commented:
here's my current attempt

WHERE TimeStamp >=  CAST(DATEDIFF(SECOND, '19700101', DATEADD(hh, -3, CAST(GETDATE() AS date))) AS bigint) * 1000

Open in new window

0
 
CAMPzxzxDeathzxzxAuthor Commented:
The datepart hour is not supported by date function dateadd for data type date.
The statement has been terminated.
0
 
CAMPzxzxDeathzxzxAuthor Commented:
This project is a 60 day sprint with a $$bonus if I complete on time.  To say I'm a little ragged right now is an understatement.  Sorry for making a mess of this one!

I've got it working now - I think!  Notice the <= instead of >= and also I had to compensate for my server's location in -5 UTC zone.

WHERE TimeStamp <=  CAST(DATEDIFF(SECOND, '19700101', DATEADD(hh, -8, GETUTCDATE())) AS bigint) * 1000

Open in new window

0
 
CAMPzxzxDeathzxzxAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.