SQL to set date/time period to 1600-1559 (dynamic)

Question

Post reply

SQL to set date/time period to 1600-1559 (dynamic)

Nick_BB

SSC Enthusiast

Points: 114
More actions
April 5, 2023 at 11:25 am

#4169433

Hi
I have a View that shows orders entered into a system using GETDATE(). However, I need it to show all orders entered between 1600 and 1559 as the 24 hour period. This then displays in a spreadsheet

So at 1610 today it would just show anything entered since 1600 today
And at 1559 tomorrow to show anything enter since 1600 yesterday until 1559 today.
I just cannot work out how to make this dynamic.

TIA

April 7, 2023 at 9:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

If you're on 2022 then you could use DATE_BUCKET:

WHERE YourDateTimeColumn >= DATE_BUCKET(DAY,1, GETDATE(), CONVERT(datetime,'19000101 16:00:00'))
  AND YourDateTimeColumn < DATEADD(DAY, 1, DATE_BUCKET(DAY,1, GETDATE(), CONVERT(datetime,'19000101 16:00:00')))

Another method would be to use some "date maths":

WHERE YourDateTimeColumn >= DATEADD(HOUR, 16,CONVERT(datetime,CONVERT(date,DATEADD(HOUR, -16, GETDATE()))))
  AND YourDateTimeColumn < DATEADD(HOUR, 40,CONVERT(datetime,CONVERT(date,DATEADD(HOUR, -16, GETDATE()))))

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Nick_BB SSC Enthusiast Points: 114 More actions · Answer 2

Thank you so much. That has done exactly what I need.

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 3

Thom A wrote:

If you're on 2022 then you could use DATE_BUCKET:

WHERE YourDateTimeColumn >= DATE_BUCKET(DAY,1, GETDATE(), CONVERT(datetime,'19000101 16:00:00'))
  AND YourDateTimeColumn < DATEADD(DAY, 1, DATE_BUCKET(DAY,1, GETDATE(), CONVERT(datetime,'19000101 16:00:00')))

Another method would be to use some "date maths":

WHERE YourDateTimeColumn >= DATEADD(HOUR, 16,CONVERT(datetime,CONVERT(date,DATEADD(HOUR, -16, GETDATE()))))
  AND YourDateTimeColumn < DATEADD(HOUR, 40,CONVERT(datetime,CONVERT(date,DATEADD(HOUR, -16, GETDATE()))))

Nicely done, Thom. The "date maths" method does a million rows to a secondary table in about 289ms for both formulas... same speed as the DATE_BUCKET method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99133 More actions · Answer 4

That (now removed) ChatGPT "solution" was amazing garbage...