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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
CAMPzxzxDeathzxzxAuthor Commented:
How should I write this?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Scott PletcherSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.