October 25, 2017 at 12:36 am
Where clause looks wrong. OP wants all data from 23:00 to 7:00 the following morning.
You are absolutely right Lynn. I didn't catch until way after I'd posted.
What's worse, the NOT between, isn't SARGEable, so there's point in having the calculated column. 🙁
October 25, 2017 at 3:16 am
Jason A. Long - Wednesday, October 25, 2017 12:36 AMWhere clause looks wrong. OP wants all data from 23:00 to 7:00 the following morning.
You are absolutely right Lynn. I didn't catch until way after I'd posted.
What's worse, the NOT between, isn't SARGEable, so there's point in having the calculated column. 🙁
Yes, and if the sample data is truly representative then two thirds of the data satisfies the predicate anyway, so an index scan is probably going to be appropriate.
John
October 25, 2017 at 7:32 am
Let's try the following, which adds one record to the original poster's data so that I could be sure my code would pick it up, and it will also identify a potential edge case - in that the data is timed at exactly 7 am, so the question is whether or not such a record should be included. If not, then the second condition in the query's WHERE clause can be removed:CREATE TABLE #mytable (
i_ticket_id int NOT NULL PRIMARY KEY CLUSTERED,
c_grand_total smallmoney NOT NULL,
c_payment_total smallmoney NOT NULL,
dt_close_time datetime NOT NULL
);
GO
INSERT INTO #mytable (i_ticket_id,c_grand_total,c_payment_total,dt_close_time)
VALUES (215670,0.00,0.00,'2017-10-23 22:03:24.553'),
(215644,133.28,133.28,'2017-10-23 22:19:57.710'),
(215671,42.61,42.61,'2017-10-23 22:27:04.323'),
(215673,45.42,45.42,'2017-10-23 22:27:38.307'),
(215672,0.00,0.00,'2017-10-23 22:31:52.507'),
(215666,68.83,68.83,'2017-10-23 23:00:19.120'),
(215610,412.96,412.96,'2017-10-23 23:01:17.790'),
(215654,26.22,26.22,'2017-10-23 23:02:30.340'),
(215636,180.26,180.26,'2017-10-23 23:25:19.383'),
(215663,30.59,30.59,'2017-10-23 23:25:47.847'),
(215677,49.16,49.16,'2017-10-23 23:26:26.113'),
(215675,42.61,42.61,'2017-10-23 23:27:44.350'),
(215667,10.92,10.92,'2017-10-23 23:28:24.357'),
(215674,5.46,5.46,'2017-10-23 23:28:49.140'),
(215621,57.90,57.90,'2017-10-23 23:29:22.160'),
(215637,159.50,159.50,'2017-10-23 23:35:11.973'),
(215676,34.96,34.96,'2017-10-23 23:55:46.433'),
(215678,90.00,90.00,'2017-10-24 14:33:51.800'),
(999999,0.00,0.00,'2017-10-25 07:00:00.000');
SELECT *, CONVERT(time, M.dt_close_time) AS TIME_OF_DAY
FROM #mytable AS M
WHERE DATEPART(hour, M.dt_close_time) IN (0,1,2,3,4,5,6,23)
OR CONVERT(time, M.dt_close_time) = CONVERT(time, '07:00:00.000');
DROP TABLE #mytable;
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
October 25, 2017 at 10:11 am
Lynn Pettis - Tuesday, October 24, 2017 7:29 PMTheSQLGuru - Tuesday, October 24, 2017 7:13 PMIf the data is actually stored as datetime, I think datepart(hour) is the way to go. If it IS actually stored as varchar(23) then just do a substring to pull out the fixed-location-two-digit hour, cast to tinyint and compare that like the datepart(hour) solution.Remember we are going across time boundaries.
I found a solution that is SARGable. I realized that this is similar to working with fiscal years where crossing a datetime boundary makes it appear that you need to treat a single block as two separate blocks. Many calculations become much simpler if you just adjust the block so that either end (usually the beginning) aligns with the datetime boundary. I also know that CASTing DATETIME to DATE was SARGable and wondered if that might be true of other datetime data types. Here is the solution.
SELECT
*
FROM
dbo.mytable m
WHERE
CAST(SWITCHOFFSET(CAST(dt_close_time AS DATETIMEOFFSET), '+01:00') AS TIME) <= '08:00'
It casts the DATETIME field to a DATETIMEOFFSET, adds one hour (converting 11:00 PM to midnight) by switching the offset, and then casts to a TIME. I tried this on a 250,000 row table (actually view) and it returned 20,000 rows in 0.4 seconds using an index seek.
Edit: I forgot to adjust the final time by one hour as well.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 25, 2017 at 10:18 am
drew.allen - Wednesday, October 25, 2017 10:11 AMLynn Pettis - Tuesday, October 24, 2017 7:29 PMTheSQLGuru - Tuesday, October 24, 2017 7:13 PMIf the data is actually stored as datetime, I think datepart(hour) is the way to go. If it IS actually stored as varchar(23) then just do a substring to pull out the fixed-location-two-digit hour, cast to tinyint and compare that like the datepart(hour) solution.Remember we are going across time boundaries.
I found a solution that is SARGable. I realized that this is similar to working with fiscal years where crossing a datetime boundary makes it appear that you need to treat a single block as two separate blocks. Many calculations become much simpler if you just adjust the block so that either end (usually the beginning) aligns with the datetime boundary. I also know that CASTing DATETIME to DATE was SARGable and wondered if that might be true of other datetime data types. Here is the solution.
SELECT
*
FROM
dbo.mytable m
WHERE
CAST(SWITCHOFFSET(CAST(dt_close_time AS DATETIMEOFFSET), '+01:00') AS TIME) <= '08:00'It casts the DATETIME field to a DATETIMEOFFSET, adds one hour (converting 11:00 PM to midnight) by switching the offset, and then casts to a TIME. I tried this on a 250,000 row table (actually view) and it returned 20,000 rows in 0.4 seconds using an index seek.
Edit: I forgot to adjust the final time by one hour as well.
Drew
Good, I tried to find a way to move the boundary but was getting anywhere. Need to remember this trick with DATETIMEOFFSET. Also need to remember this as the first go to answer for Jeff's "How do you get the system date and time using T-SQL?" interview question rather than GETDATE().
October 26, 2017 at 1:13 pm
That's really slick Drew - well done!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 26, 2017 at 2:16 pm
Thank you everyone!
October 26, 2017 at 5:32 pm
drew.allen - Wednesday, October 25, 2017 10:11 AMLynn Pettis - Tuesday, October 24, 2017 7:29 PMTheSQLGuru - Tuesday, October 24, 2017 7:13 PMIf the data is actually stored as datetime, I think datepart(hour) is the way to go. If it IS actually stored as varchar(23) then just do a substring to pull out the fixed-location-two-digit hour, cast to tinyint and compare that like the datepart(hour) solution.Remember we are going across time boundaries.
I found a solution that is SARGable. I realized that this is similar to working with fiscal years where crossing a datetime boundary makes it appear that you need to treat a single block as two separate blocks. Many calculations become much simpler if you just adjust the block so that either end (usually the beginning) aligns with the datetime boundary. I also know that CASTing DATETIME to DATE was SARGable and wondered if that might be true of other datetime data types. Here is the solution.
SELECT
*
FROM
dbo.mytable m
WHERE
CAST(SWITCHOFFSET(CAST(dt_close_time AS DATETIMEOFFSET), '+01:00') AS TIME) <= '08:00'It casts the DATETIME field to a DATETIMEOFFSET, adds one hour (converting 11:00 PM to midnight) by switching the offset, and then casts to a TIME. I tried this on a 250,000 row table (actually view) and it returned 20,000 rows in 0.4 seconds using an index seek.
Edit: I forgot to adjust the final time by one hour as well.
Drew
Awesome.
--Jeff Moden
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply