SQL Syntax for BETWEEN any dates and specific time

Question

SQL Syntax for BETWEEN any dates and specific time

alexdkm

SSC Enthusiast

Points: 112
More actions
November 8, 2022 at 7:51 pm

#4110211

hi all i want to ask how to select from any dates and spesific time
id date
1 2022-01-01 00:00:00
2 2022-01-02 05:00:00
3 2022-01-03 09:00:00
i want to select from 00:00:00 - 06:00:00
how to solve this? thanks in advance
November 9, 2022 at 5:54 am

This was removed by the editor as SPAM
November 12, 2022 at 9:48 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

pietlinden SSC Guru Points: 63519 More actions · Answer 1

I would think you should either split the date / time column into two columns - one with just the date and the other with just the time, or use computed columns to get them. Then you can index the time column, so it can be queried properly.

Adamsmith95 Newbie Points: 2 More actions · Answer 2

You should be able to use:

WHERE CAST(date AS TIME) BETWEEN '00:00:00' AND '06:00:00'

ScottPletcher SSC Guru Points: 101134 More actions · Answer 3

WHERE DATEPART(HOUR, datetime_column) BETWEEN 0 AND 6

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

MarkP Old Hand Points: 305 More actions · Answer 4

Scott, won't you include all times between 6 and 7 with that? 06:32 for example?

I'd propose something like:

DATEDIFF(SECOND,CAST(date_col AS DATE),date_col) < 21600

where you return all rows where there are fewer than 21600 seconds elapsed since the start of the day.

ScottPletcher SSC Guru Points: 101134 More actions · Answer 5

Yes, it would. I took:

i want to select from 00:00:00 - 06:00:00

to mean a contiguous range from 00 to 06, but I likely should have used 00 and 05, if they don't want 06 times included:

WHERE DATEPART(HOUR, datetime_column) BETWEEN 0 AND 5

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

DesNorton SSC-Insane Points: 24424 More actions · Answer 6

I would rather use

WHERE CAST(date AS  TIME) >= '00:00:00'
  AND CAST(date AS  TIME) < '06:00:00'

How to post data/code on a forum to get the best help.

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 7

WHERE cast(datetime_column AS time(0)) <= '06:00:00'

If you don't want to include 06:00:00 - then rewrite it as:

WHERE cast(datetime_column AS time(0)) < '06:00:00'

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 8

Most of these are not SARGable. I wonder if the TIME conversion is (Like DATE and DATETIME supposedly is).

If this is a regular thing, I'm with Piet... persisted computed column.

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

Jonathan AC Roberts SSCoach Points: 18380 More actions · Answer 9

As a test I used this date range function https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function to insert 6.3M rows into a temporary table to see which is faster. There doesn't seem to be lot of difference between any of them.

set statistics io, time off
drop table if exists #x;
go
select * 
into #x
from dbo.DateRange('20200101','20220101','ss',1)
set statistics time on
go
select count(* )
from #x
WHERE CAST(Value AS  TIME) BETWEEN '00:00:00' AND '06:00:00'
go
select count(* )
from #x
WHERE DATEPART(HOUR, Value) BETWEEN 0 AND 5
go
select count(* )
from #x
WHERE DATEDIFF(SECOND,CAST(Value AS DATE),Value) < 21600

ScottPletcher SSC Guru Points: 101134 More actions · Answer 10

I'd be surprised if an index on a computed column ultimately helped the query plan at all. Unless you have a fully covering index, which would likely duplicate most of the table here. And you have to worry about the restrictions imposed when you modify any table with an index(es) on a computed column. Thus, sadly, it's possible you will still have to do scans of the table, or very large parts of it, to satisfy this query.

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: 1004441 More actions · Answer 11

Agreed.

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