SQL Select only business hours data

Question

SQL Select only business hours data

Viewing 6 posts - 16 through 20 (of 20 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Since you're only checking for an hour range, you can use BETWEEN. It's just as accurate and easier to code and read.

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".

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

ScottPletcher wrote:

Since you're only checking for an hour range, you can use BETWEEN. It's just as accurate and easier to code and read.

I agree that it's real tempting to use something so simple. The reason why I usually don't isn't for me... it's for the next person when management changes their mind(s) and says something like "We want to change it to 5:30PM". Will management ever say that? It's impossible for anyone to predict that but, if they do (as they've been know to do so often in the past), the next person (who frequently doesn't understand closed/open ranges) won't have any difficulty. Yep... you'll think that's ridiculous... until someone screws it up and you're the one that has to not only fix that code, but the downstream damage, as well.

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Jeff Moden wrote:

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.

What do you suggest if the end time is midnight ?

Doesn't '17:59:59' exclude 1 second, not 1 minute ?

This reply was modified 2 years, 8 months ago by homebrew01.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

Jeff Moden wrote:

ScottPletcher wrote:
Since you're only checking for an hour range, you can use BETWEEN. It's just as accurate and easier to code and read.
I agree that it's real tempting to use something so simple. The reason why I usually don't isn't for me... it's for the next person when management changes their mind(s) and says something like "We want to change it to 5:30PM". Will management ever say that? It's impossible for anyone to predict that but, if they do (as they've been know to do so often in the past), the next person (who frequently doesn't understand closed/open ranges) won't have any difficulty. Yep... you'll think that's ridiculous... until someone screws it up and you're the one that has to not only fix that code, but the downstream damage, as well.

Whoever does the change to 17:30 should be able to change the comparison method. Help should be available in your shop on how to do that; I know I've made it available in mine. If they're still too incompetent to do it, hopefully they (1) realize that and get help (2) you realize that and get rid of them if they just can't make changes that simple.

Personally I think someone who's that careless is more likely to get a coded time range wrong than an hour range. That's just my view on it.

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".

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

ScottPletcher wrote:

If they're still too incompetent to do it, hopefully they (1) realize that and get help (2) you realize that and get rid of them if they just can't make changes that simple./quote]

Understood but, for (1), they don't (Dunning-Kruger in full effect a lot of the time) and neither do their bosses and (2), I'm thankfully no longer in such a position and haven't been for over a decade. And so I 'protect" where I can.

And I totally get what you're saying. I get ticked off about people justifying that you have to spell out year, month, day, hour, minute and second because "no one understand the abbreviations). Heh... I do draw the line there.

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

Jeff Moden wrote:

ScottPletcher wrote:

If they're still too incompetent to do it, hopefully they (1) realize that and get help (2) you realize that and get rid of them if they just can't make changes that simple./quote]
Understood but, for (1), they don't (Dunning-Kruger in full effect a lot of the time) and neither do their bosses and (2), I'm thankfully no longer in such a position and haven't been for over a decade. And so I 'protect" where I can.
And I totally get what you're saying. I get ticked off about people justifying that you have to spell out year, month, day, hour, minute and second because "no one understand the abbreviations). Heh... I do draw the line there.

Interesting. I DO spell out the date/time part names -- as you'll see in my code all over this site -- because I think it's clearer. Other than MCS/MS, just 'cause the full names for those are really long.

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".