August 12, 2013 at 1:38 pm
I want records for datetimes between 01/01/2000 (after midnight) through 01/03/2000 (through midnight). Is the following predicate the correct one to use?
WHERE orderdate BETWEEN '01/01/2000 00:00:00:000 AND '01/03/2000 00:00:00.000'
or will this also return records from midnight of '01/01/2000' ?
Thank you.
Note: This is for study purposes. I would like to test this for myself, but don't know how to create a table with the sample data needed to test this. If instead of answering someone prefers to point me to a resource for generating a table with a datetime column containing several thousands of dates that are guaranteed to fall within midnight->minute after midnight range, I would also appreciate.
--Quote me
August 12, 2013 at 1:51 pm
BETWEEN uses inclusive ranges, so any date equal to '01/01/2000 00:00:00:000' will also be selected.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 12, 2013 at 3:04 pm
polkadot (8/12/2013)
I want records for datetimes between 01/01/2000 (after midnight) through 01/03/2000 (through midnight). Is the following predicate the correct one to use?WHERE orderdate BETWEEN '01/01/2000 00:00:00:000 AND '01/03/2000 00:00:00.000'
or will this also return records from midnight of '01/01/2000' ?
The Where clause you have specified will NOT return any row that has a date of 01/03/2000 if the time portion is anything other than 0's. I've learned it is best to go out 1 day further to make sure my date range includes the records I want.
declare @begin_date datetime = '01/01/2000'
declare @end_date datetime = '01/03/2000'
Where OrderDate >= @begin_date and OrderDate < DATEADD(dd, 1, @end_Date)
The above where clause will return all rows that have a date between 01/01/2000 and 01/03/2000 regardless of the time.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 13, 2013 at 7:38 am
Or you can cast your datetime column as date.
WHERE cast(orderdate as date) BETWEEN '01/01/2000' AND '01/03/2000'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 10:18 am
Sean Lange (8/13/2013)
Or you can cast your datetime column as date.
WHERE cast(orderdate as date) BETWEEN '01/01/2000' AND '01/03/2000'
Would work well for any query that returned somewhere around less than 10K rows, but sargability issues would kick in and slow things down on anything above that very soft ceiling! (Depending upon CPU(s), RAM, etc...)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 13, 2013 at 10:54 am
LinksUp (8/12/2013)
polkadot (8/12/2013)
I want records for datetimes between 01/01/2000 (after midnight) through 01/03/2000 (through midnight). Is the following predicate the correct one to use?WHERE orderdate BETWEEN '01/01/2000 00:00:00:000 AND '01/03/2000 00:00:00.000'
or will this also return records from midnight of '01/01/2000' ?
The Where clause you have specified will NOT return any row that has a date of 01/03/2000 if the time portion is anything other than 0's. I've learned it is best to go out 1 day further to make sure my date range includes the records I want.
declare @begin_date datetime = '20000101'
declare @end_date datetime = '20000103'
Where OrderDate >= @begin_date and OrderDate < DATEADD(dd, 1, @end_Date)
The above where clause will return all rows that have a date between 01/01/2000 and 01/03/2000 regardless of the time.
Actually, to meet the OPs requirement you have to make one slight change:
declare @begin_date datetime = '01/01/2000'
declare @end_date datetime = '01/03/2000'
Where OrderDate > @begin_date and OrderDate <= DATEADD(dd, 1, @end_Date)
August 13, 2013 at 11:43 am
Lynn Pettis (8/13/2013)I want records for datetimes between 01/01/2000 (after midnight) through 01/03/2000 (through midnight).
WHERE orderdate BETWEEN '01/01/2000 00:00:00:000 AND '01/03/2000 00:00:00.000'
After re-reading the requirements, I can see how it could be > beg_date. But it does not make sense. Why not just specify 01/02/2000 as the beginning date? I am guessing that the OP spec is wrong (OP, Please chime in here if I am wrong!) and they wanted inclusive ranges as the end_date suggested. So the spec could be after midnight of the day prior to the begin_date.
Looking closer at the written description and comparing to the where clause, there seems to be some differences. Clarification is definitely needed.
I also noted that you changed DateAdd to <=. I think that will give you too much data.
Play with the following code and see what you get:
declare @begin_date datetime = '2000-01-01'
declare @end_date datetime = '2000-01-03'
declare @timeTable table
(
testTime DateTime
)
insert @timeTable (testTime)
values
('1999-12-31 23:59:59.997')
,('2000-01-01 00:00:00.000')
,('2000-01-01 23:59:59.997')
,('2000-01-02 00:00:00.000')
,('2000-01-03 00:00:00.000')
,('2000-01-03 23:59:59.997')
,('2000-01-04 00:00:00.000')
,('2000-01-04 00:00:00.005')
select testTime from @timeTable
Where testTime >= @begin_date and testTime < DATEADD(dd, 1, @end_Date)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 14, 2013 at 3:48 am
LinksUp (8/13/2013)
Lynn Pettis (8/13/2013)I want records for datetimes between 01/01/2000 (after midnight) through 01/03/2000 (through midnight).
WHERE orderdate BETWEEN '01/01/2000 00:00:00:000 AND '01/03/2000 00:00:00.000'
After re-reading the requirements, I can see how it could be > beg_date. But it does not make sense. Why not just specify 01/02/2000 as the beginning date? I am guessing that the OP spec is wrong (OP, Please chime in here if I am wrong!) and they wanted inclusive ranges as the end_date suggested. So the spec could be after midnight of the day prior to the begin_date.
Looking closer at the written description and comparing to the where clause, there seems to be some differences. Clarification is definitely needed.
I also noted that you changed DateAdd to <=. I think that will give you too much data.
Play with the following code and see what you get:
declare @begin_date datetime = '2000-01-01'
declare @end_date datetime = '2000-01-03'
declare @timeTable table
(
testTime DateTime
)
insert @timeTable (testTime)
values
('1999-12-31 23:59:59.997')
,('2000-01-01 00:00:00.000')
,('2000-01-01 23:59:59.997')
,('2000-01-02 00:00:00.000')
,('2000-01-03 00:00:00.000')
,('2000-01-03 23:59:59.997')
,('2000-01-04 00:00:00.000')
,('2000-01-04 00:00:00.005')
select testTime from @timeTable
Where testTime >= @begin_date and testTime < DATEADD(dd, 1, @end_Date)
Remember what the OP requested, All records after Midnight of the start date until and including Midnight of the end date. So 2000-01-01 00:00:00 TO 2000-01-03 00:00:00 means all records after 2000-01-01 00:00:00 (not including midnight) to 2000-01-03 00:00:00 (including midnight).
This means > @StartDate and <= @EndDate.
August 15, 2013 at 11:30 am
Koen Verbeeck (8/12/2013)
BETWEEN uses inclusive ranges, so any date equal to '01/01/2000 00:00:00:000' will also be selected.
Thanks, Koen Verbeeck. You answered very succinctly, which I appreciate very much, and it helped me finally understand what IB meant when he wrote that datetime has a precision of three and a third milliseconds….Your way was easier to ingest, for first round.
Building on LinkedUp’s DDL I was able to check that the result set included midnight through midnight whether I used date ('01/01/2000’) or datetime ('01/01/2000 00:00:00:000') form.
LinksUp
You said
LinksUp (8/12/2013)
The Where clause you have specified will NOT return any row that has a date of 01/03/2000 if the time portion is anything other than 0's. I've learned it is best to go out 1 day further to make sure my date range includes the records I want.
That was not my experience. As you can see my where clause end date contains numbers other than zeros and still returns inclusive range, albeit rounds up.
create table testTime
(testDate datetime);
insert testTime
values
('1999-12-31 23:59:59.997')
,('2000-01-01 00:00:00.000')
,('2000-01-01 23:59:59.997')
,('2000-01-02 00:00:00.000')
,('2000-01-03 00:00:00.000')
,('2000-01-03 23:59:59.997')
,('2000-01-04 00:00:00.000')
,('2000-01-04 00:00:00.005')
,('2000-01-01 12:30:00.000')
,('2000-01-02 16:30:00.000')
,('2000-01-03 23:59:59.997')
,('2000-01-02 23:59:59.997')
,('2000-01-02 23:59:59.999')
,('2000-01-02 23:59:59.999');
SELECT [testDate]
FROM [Sandbox].[dbo].[testTime]
where testDate BETWEEN '01/01/2000' AND '01/03/2000 23:59:59.997'
returned these results:
2000-01-01 00:00:00.000
2000-01-01 23:59:59.997
2000-01-02 00:00:00.000
2000-01-03 00:00:00.000
2000-01-03 23:59:59.997
2000-01-01 12:30:00.000
2000-01-02 16:30:00.000
2000-01-03 23:59:59.997
2000-01-02 23:59:59.997
2000-01-03 00:00:00.000
2000-01-03 00:00:00.000
Thanks for bringing up SARGability, LinkedUp. Doesn’t that mean I should avoid *both* CAST and DATEADD in the predicate?
I also noticed that when I insert a date like ('2000-01-02 23:59:59.999') into a datetype column of datetime, it gets rounded to 2000-01-03 00:00:00.000. To avoid that and improve precision I'd have to use datetime2 datatype…but that’s going off topic.
Thanks to each of you who helped further the discussion into meaningful territory :-). I think the use of operators like >=< is good idea to avoid the rounding that happens with BETWEEN. IB also says that.
Oh, btw, IB also says to use the language-neutral form of date '20000101' instead of '01/01/2000' like LinkedUp did, which always gets interpreted as ymd, regardless of language set for your logon. So, that's another good takeaway.
Going forward I think I am going to use operators instead of BETWEEN and use the date format of the column, to avoid ambiguity.
--Quote me
August 15, 2013 at 1:23 pm
LinksUp
You said
The Where clause you have specified will NOT return any row that has a date of 01/03/2000 if the time portion is anything other than 0's. I've learned it is best to go out 1 day further to make sure my date range includes the records I want.
That was not my experience. As you can see my where clause end date contains numbers other than zeros and still returns inclusive range, albeit rounds up.
That statement refers to your original WHERE clause from your OP. If the time portion is something other than 0's in your ROWS (DATA), then they will be filtered.
For example, if a query resolved to the following for one row using your original WHERE clause:
WHERE 2000-01-03 15:23:20.444 < 2000-01-03 00:00:00.000
it would be filtered.
When getting rows based on the date, going out 1 day further eliminates having to worry about the time portion of the variable. You demonstrated that principle perfectly in your altered WHERE statement when you changed the ending time from all 0's to 23:59:59.997. That will include all rows that have a date of 2000-01-03 regardless of the time portion.
So instead of having to always add the time portion of 23:59 etc.. to my where clauses, I use DateAdd(dd, 1, @end_date), effectively removing the time element from the compare.
Thanks for bringing up SARGability, LinkedUp. Doesn’t that mean I should avoid *both* CAST and DATEADD in the predicate?
You should avoid using any functions on the left side of the compare. Since CAST was on the left hand side of the compare, SARGability would be an issue. With DateAdd on the right, it computes it once and done.
Oh, btw, IB also says to use the language-neutral form of date '20000101' instead of '01/01/2000' like LinkesUp did, which always gets interpreted as ymd, regardless of language set for your logon. So, that's another good takeaway.
That was a typo of my part! Dates should always be in ISO format of yyyy-mm-dd
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply