SQL Select only business hours data

Question

Post reply

SQL Select only business hours data

yogi123

SSCrazy

Points: 2932

March 24, 2022 at 3:33 pm

#4005437

Hi there,

I have task to select only data falls within business hours, meaning 9 am to 5 pm

CREATE TABLE #TEMP
(ID INT, LOADDATE DATETIME)

INSERT INTO #TEMP
SELECT 1,'2022-03-24 10:26:02.000'
SELECT 2,'2022-03-22 18:26:02.000'
SELECT 3,'2022-03-21 12:26:02.000'
SELECT 4,'2022-03-22 17:26:02.000'
SELECT 5,'2022-03-21 22:26:02.000'
SELECT 6,'2022-03-24 21:26:02.000'

The output should be

ID LOADDATE

1 2022-03-24 10:26:02.000

3 2022-03-21 12:26:02.000

Thanks!

Viewing 15 posts - 1 through 15 (of 20 total)

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

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

What have you tried? This is actually a very simple query.

Have you looked at the various tools available in SQL related to dates and times?

https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver15

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

yogi123 SSCrazy Points: 2932 More actions · Answer 2

I did tried some, but didn't got desired output what i am looking for.

Thanks!

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

Can you post what you tried? It's far easier us, and more beneficial to you, if we can see that and use it to teach you.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

yogi123 SSCrazy Points: 2932 More actions · Answer 4

I think this might solve this

SELECT *

FROM #TEMP

WHERE cast(LOADDATE as time) BETWEEN '9:00' AND '17:00'

yogi123 SSCrazy Points: 2932 More actions · Answer 5

Basically actual ask is,

we need to display the date, and rule is the time rage is 9AM to 8:59AM,

CREATE TABLE #TEMP
(ID INT, LOADDATE DATETIME)

INSERT INTO #TEMP
SELECT 1,'2022-03-24 8:26:02.000'
UNION
SELECT 2,'2022-03-22 9:26:02.000'
UNION
SELECT 3,'2022-03-21 12:26:02.000'
UNION
SELECT 4,'2022-03-22 17:26:02.000'
UNION
SELECT 5,'2022-03-21 22:26:02.000'
UNION
SELECT 6,'2022-03-24 07:26:02.000'


IDLOADDATEdate should be
12022-03-24 08:26:02.0002022-03-23
22022-03-22 09:26:02.0002022-03-24
32022-03-21 12:26:02.0002022-03-21 
42022-03-22 17:26:02.0002022-03-22 
52022-03-21 22:26:02.0002022-03-21 
62022-03-24 07:26:02.0002022-03-23

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

yogi123 wrote:

Hi there,
I have task to select only data falls within business hours, meaning 9 am to 5 pm
CREATE TABLE #TEMP
(ID INT, LOADDATE DATETIME)

INSERT INTO #TEMP
SELECT 1,'2022-03-24 10:26:02.000'
SELECT 2,'2022-03-22 18:26:02.000'
SELECT 3,'2022-03-21 12:26:02.000'
SELECT 4,'2022-03-22 17:26:02.000'
SELECT 5,'2022-03-21 22:26:02.000'
SELECT 6,'2022-03-24 21:26:02.000'
The output should be
ID LOADDATE 1 2022-03-24 10:26:02.000 3 2022-03-21 12:26:02.000
Thanks!

Is 5PM supposed to be inclusive or exclusive?

--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)

yogi123 SSCrazy Points: 2932 More actions · Answer 7

below is to get business hours data

SELECT *

FROM #TEMP

WHERE CAST(LOADDATE AS TIME) BETWEEN '9:00:00' AND '17:59:59'

Thanks for your help

yogi123 SSCrazy Points: 2932 More actions · Answer 8

also got solution for this one as well

select ID,

CONVERT(varchar(20),LOADDATE,101) as [Date],

CASE WHEN CAST(LOADDATE AS TIME) > '8:59:59' THEN CONVERT(varchar(20),LOADDATE,101)

ELSE CONVERT(varchar(20),DATEADD(DAY,-1,LOADDATE),101) END AS [NewDate],

CAST(LOADDATE AS TIME) as time

from #temp

Michael L John One Orange Chip Points: 27055 More actions · Answer 9

yogi123 wrote:

below is to get business hours data
SELECT * FROM #TEMP WHERE CAST(LOADDATE AS TIME) BETWEEN '9:00:00' AND '17:59:59'
Thanks for your help

Your original post asked for records that fall between business hours, 9 AM to 5 PM. The above query will return records from 9 AM till 5:59 PM. Is that what you want? What about records created at 5:59:350?

I do not know how busy your system may be, or when records are created, but you are also running the risk of missing records by doing things like this: "CASE WHEN CAST(LOADDATE AS TIME) > '8:59:59' "

Why? Do you want to INCLUDE 9:00 AM? Then do CASE WHEN CAST(LOADDATE AS TIME) >= '9:00:000' If you want only records AFTER 9 AM, then do CASE WHEN CAST(LOADDATE AS TIME) > '9:00:000'.

Remove all possibility of missing records. Perform the correct calculation, not an estimation. If this was a homework problem, I would mark it incorrect.

Also, by performing a CAST on a column in the WHERE clause, you have created a non-sargable where clause. Again, depending upon how busy your system is and how large the table(s) are, this can result in a very poor performing query.

This reply was modified 2 years, 8 months ago by Michael L John.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ScottPletcher SSC Guru Points: 101098 More actions · Answer 10

To make changes easier -- and the code somewhat clearer to read -- I usually use this method for that:

WHERE DATEPART(HOUR, LOADDATE) BETWEEN 9 AND 17

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

yogi123 SSCrazy Points: 2932 More actions · Answer 11

yogi123

SSCrazy

Points: 2932

March 25, 2022 at 4:07 pm

#4006007

Thank You ...

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

yogi123 wrote:

below is to get business hours data
SELECT * FROM #TEMP WHERE CAST(LOADDATE AS TIME) BETWEEN '9:00:00' AND '17:59:59'
Thanks for your help

My recommendation is to get out of the habit of using BETWEEN for temporal ranges. From what you wrote above, you're really saying that the first time that you don't want is 18:00. The problem is, you've excluded almost the last whole minute of the time frame you want and it's a common issue with BETWEEN. Instead, I recommend getting into the habit of writing the temporal range as inclusive/exclusive also known as "closed/open" after notations on a GANT chart.

In other words, write it like if you want from an including 9AM up to and not including 6PM,

 SELECT t.*
   FROM #TEMP t
  CROSS APPLY (VALUES(CAST(LOADDATE AS TIME(0))))ca(LoadToD)
  WHERE ca.LoadTod >= '9:00' AND ca.LoadTod < '18:00'
;

Yes... it's a bit more complicated than using BETWEEN especially when you have to use a conversion but... it's a habit to get into that will "save your life" someday. I was a mistake that I made in my very early days and it took me a couple of days to undo all the damage I had done.

--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)

yogi123 SSCrazy Points: 2932 More actions · Answer 13

yogi123

SSCrazy

Points: 2932

March 25, 2022 at 4:32 pm

#4006014

Thanks Jeff!!!

Michael L John One Orange Chip Points: 27055 More actions · Answer 14

Yes... it's a bit more complicate than using BETWEEN especially when you have to use a conversion but... it's a habit to get into that will "save your life" someday. I was a mistake that I made in my very early days and it took me a couple of days to undo all the damage I had done.

Two of many stories related to incorrect usage of dates, times, and comparisons...

Story one:

At a previous company, one of the accountants asked the question about the monthly revenue and the monthly expenditure reports. They said the sum of each month's reports never match the value if we run it for the full year.

Within seconds of opening up the stored proc, I saw that they were using BETWEEN on a datetime column. The first and last day of the month was calculated, and the code was Datetime_Column BETWEEN @StartDate and @EndDate. The time portion of the datetime column was not considered, which left off all business for everything after midnight on the last day of the month.

The nature of the business was that the last 10 days of the month the majority of the sales occurred. They had been understating revenue and expenses for years.

Story two:

The developers got into the really bad habit of concatenating "23:59" onto the ending dates to include those records. This was a very busy medical system. There were always "missing records" that occurred in that one minute.

BETWEEN would not fix this. Like Jeff said, use a closed interval.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/