UTC millisecond Cast DateAdd Hours error

CAMPzxzxDeathzxzx
CAMPzxzxDeathzxzx used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Don't cast as date, because date doesn't have hours:

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

Author

Commented:
I don't understand?

Author

Commented:
How should I write this?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
WHERE [TimeStamp] < DATEADD(ms, -10800000, GETUTCDATE())
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.

Author

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"

Author

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.

Author

Commented:
elapsed ms since 1970-01-01 (unix style date)

Author

Commented:
Please

Author

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

Author

Commented:
The datepart hour is not supported by date function dateadd for data type date.
The statement has been terminated.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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.

Author

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

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial