September 18, 2018 at 10:20 am
Hello,
I am looking to create a query that will get data between Yesterday at 6AM until 1AM today. Is there a way I can specifically set a time range?
DECLARE @StartDt datetime, @EndDt datetime
SET @StartDt = getdate()-1
SET @EndDt = getDate()
PRINT @StartDt
PRINT @EndDt
-- Desired Results:
-- @StartDt Yesterday at 6AM
-- @EndDt Today at 1AM
Many Thanks in advance!
September 18, 2018 at 11:47 am
SELECT
@StartDt = DATEADD(HOUR, 6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)),
@EndDt = DATEADD(HOUR, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 18, 2018 at 11:56 am
This is shorter, but many people don't like it for some reason.
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '06:00'),
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '01:00')
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 18, 2018 at 12:10 pm
drew.allen - Tuesday, September 18, 2018 11:56 AMThis is shorter, but many people don't like it for some reason.
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '06:00'),
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '01:00')Drew
For me, it breaks the normal pattern of the code. The standard method to strip time is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
I prefer to keep the standard, instantly-recognized code rather than break it just for the sake of a few letters.
But if we're going for shortness, shouldn't we go for max shortness?
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -0.75)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 18, 2018 at 3:08 pm
ScottPletcher - Tuesday, September 18, 2018 12:10 PMdrew.allen - Tuesday, September 18, 2018 11:56 AMThis is shorter, but many people don't like it for some reason.
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '06:00'),
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '01:00')Drew
For me, it breaks the normal pattern of the code. The standard method to strip time is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)I prefer to keep the standard, automatically-recognized rather than break it just for the sake of a few letters.
Typically you use the -1 date trick, which I don't like for the same reason, but if we're going for shortness, shouldn't we go for max shortness?SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -0.75)
I think that this pattern is too powerful to relegate to only using the orthodox pattern. It's also not that difficult to recognize this more flexible pattern once you start using it.
Also, the reason for brevity is that it aids in understanding not just for brevity's sake. The fewer operations that are employed, the fewer things that you need to untangle before understanding it. Using -0.75 to represent 6:00 AM on the day before does not aid in understanding, so it should not be used.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 2, 2018 at 6:02 am
People worry about code readability and that's a good thing but they forget one of the most important tools there is that makes even the most obtuse code (sometimes necessary for performance) easy to read.
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '06:00') --06AM Yesterday
,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '01:00') --01AM Today
;
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -0.75) --06AM Yesterday
,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0.04166666666666666666666666666667)--01AM Today
;
SELECT DATEADD(hh,6,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1)) --06AM Yesterday
,DATEADD(hh,1,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)) --01AM Today
;
SELECT DATEADD(hh,6,FLOOR(CONVERT(FLOAT,GETDATE()))-1) --06AM Yesterday
,DATEADD(hh,1,FLOOR(CONVERT(FLOAT,GETDATE())) ) --01AM Today
;
Write the code for the computer. Write the comments for the human. It takes no time at all to make things crystal clear to the human.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2018 at 3:41 pm
I would only declare and set the start datetime - then calculate the end datetime. With that said - do you want everything up to 1am but not including 1am? Or every time in the 1am hour?
Declare @startDate datetime = dateadd(day, datediff(day, 0, getdate()) - 1, '06:00');
Select ... From ... Where somedate >= @startDate And somedate < dateadd(hour, 19, @startDate); --up to 1am, non-inclusive
Declare @startDate datetime = dateadd(day, datediff(day, 0, getdate()) - 1, '06:00');
Select ... From ... Where somedate >= @startDate And somedate < dateadd(hour, 20, @startDate); --including all times in the 1am hour
If you use equal to for the end range - you would only include those times that occurred exactly at midnight.
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
November 6, 2018 at 6:29 am
Jeff Moden - Friday, November 2, 2018 6:02 AMPeople worry about code readability and that's a good thing but they forget one of the most important tools there is that makes even the most obtuse code (sometimes necessary for performance) easy to read.
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '06:00') --06AM Yesterday
,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '01:00') --01AM Today
;
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -0.75) --06AM Yesterday
,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0.04166666666666666666666666666667)--01AM Today
;
SELECT DATEADD(hh,6,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1)) --06AM Yesterday
,DATEADD(hh,1,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)) --01AM Today
;
SELECT DATEADD(hh,6,FLOOR(CONVERT(FLOAT,GETDATE()))-1) --06AM Yesterday
,DATEADD(hh,1,FLOOR(CONVERT(FLOAT,GETDATE())) ) --01AM Today
;Write the code for the computer. Write the comments for the human. It takes no time at all to make things crystal clear to the human.
Not all methods are equal, the conversion to float is the least equal of them all!
😎
-- Don't use this one
DATEADD(hh,1,FLOOR(CONVERT(FLOAT,GETDATE())) )
-- The two best ones
DATEADD(HOUR,6,CONVERT(DATETIME,CONVERT(DATE,(GETDATE()-1),0),0))
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -0.75)
November 6, 2018 at 7:46 am
drew.allen - Tuesday, September 18, 2018 3:08 PMScottPletcher - Tuesday, September 18, 2018 12:10 PMdrew.allen - Tuesday, September 18, 2018 11:56 AMThis is shorter, but many people don't like it for some reason.
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '06:00'),
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '01:00')Drew
For me, it breaks the normal pattern of the code. The standard method to strip time is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)I prefer to keep the standard, automatically-recognized rather than break it just for the sake of a few letters.
Typically you use the -1 date trick, which I don't like for the same reason, but if we're going for shortness, shouldn't we go for max shortness?SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -0.75)
I think that this pattern is too powerful to relegate to only using the orthodox pattern. It's also not that difficult to recognize this more flexible pattern once you start using it.
Also, the reason for brevity is that it aids in understanding not just for brevity's sake. The fewer operations that are employed, the fewer things that you need to untangle before understanding it. Using -0.75 to represent 6:00 AM on the day before does not aid in understanding, so it should not be used.
Drew
Interesting. So -0.75 is confusing but -1 is perfectly clear? I don't see that. I think -1 has the same potential have-to-pause-to-figure-it-out issue.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 6, 2018 at 12:25 pm
ScottPletcher - Tuesday, November 6, 2018 7:46 AMdrew.allen - Tuesday, September 18, 2018 3:08 PMScottPletcher - Tuesday, September 18, 2018 12:10 PMdrew.allen - Tuesday, September 18, 2018 11:56 AMThis is shorter, but many people don't like it for some reason.
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '06:00'),
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '01:00')Drew
For me, it breaks the normal pattern of the code. The standard method to strip time is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)I prefer to keep the standard, automatically-recognized rather than break it just for the sake of a few letters.
Typically you use the -1 date trick, which I don't like for the same reason, but if we're going for shortness, shouldn't we go for max shortness?SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -0.75)
I think that this pattern is too powerful to relegate to only using the orthodox pattern. It's also not that difficult to recognize this more flexible pattern once you start using it.
Also, the reason for brevity is that it aids in understanding not just for brevity's sake. The fewer operations that are employed, the fewer things that you need to untangle before understanding it. Using -0.75 to represent 6:00 AM on the day before does not aid in understanding, so it should not be used.
Drew
Interesting. So -0.75 is confusing but -1 is perfectly clear? I don't see that. I think -1 has the same potential have-to-pause-to-figure-it-out issue.
Yes, -0.75 is more confusing than -1, because people have a harder time with fractions/decimals than with whole numbers.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 6, 2018 at 5:38 pm
drew.allen - Tuesday, November 6, 2018 12:25 PMScottPletcher - Tuesday, November 6, 2018 7:46 AMdrew.allen - Tuesday, September 18, 2018 3:08 PMScottPletcher - Tuesday, September 18, 2018 12:10 PMdrew.allen - Tuesday, September 18, 2018 11:56 AMThis is shorter, but many people don't like it for some reason.
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '06:00'),
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '01:00')Drew
For me, it breaks the normal pattern of the code. The standard method to strip time is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)I prefer to keep the standard, automatically-recognized rather than break it just for the sake of a few letters.
Typically you use the -1 date trick, which I don't like for the same reason, but if we're going for shortness, shouldn't we go for max shortness?SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -0.75)
I think that this pattern is too powerful to relegate to only using the orthodox pattern. It's also not that difficult to recognize this more flexible pattern once you start using it.
Also, the reason for brevity is that it aids in understanding not just for brevity's sake. The fewer operations that are employed, the fewer things that you need to untangle before understanding it. Using -0.75 to represent 6:00 AM on the day before does not aid in understanding, so it should not be used.
Drew
Interesting. So -0.75 is confusing but -1 is perfectly clear? I don't see that. I think -1 has the same potential have-to-pause-to-figure-it-out issue.
Yes, -0.75 is more confusing than -1, because people have a harder time with fractions/decimals than with whole numbers.
Drew
I'll never understand why people have that problem with simple temporal math using date serial numbers.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply