November 8, 2022 at 7:51 pm
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 8, 2022 at 10:14 pm
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.
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
December 12, 2022 at 5:23 pm
You should be able to use:
WHERE CAST(date AS TIME) BETWEEN '00:00:00' AND '06:00:00'
December 12, 2022 at 7:03 pm
WHERE DATEPART(HOUR, datetime_column) BETWEEN 0 AND 6
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 13, 2022 at 10:39 am
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.
December 13, 2022 at 7:24 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
December 13, 2022 at 7:43 pm
I would rather use
WHERE CAST(date AS TIME) >= '00:00:00'
AND CAST(date AS TIME) < '06:00:00'
December 13, 2022 at 8:20 pm
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
December 13, 2022 at 11:45 pm
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
Change is inevitable... Change for the better is not.
December 14, 2022 at 1:13 am
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
December 14, 2022 at 3:06 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
December 14, 2022 at 9:01 pm
Agreed.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply