May 15, 2015 at 12:31 am
Hi there, hope in your help.
In SQL server I have the column doTableDate set a Datetime.
I need extract all rows in on date range and I think use to syntax `Between And`
If try this version of query I have in output 889 rows all with date 2014-01-03... but I have other records with date 2014-01-04 in column doTableData...
SELECT
*
FROM
doTable
WHERE
doTableDate BETWEEN CONVERT (
datetime,
'03/01/2014 00:00:00',
103
)
AND CONVERT (
datetime,
'04/01/2014 00:00:00',
103
);
If try this version I don't have output no record, why?
The syntax `Between And` not working in SQL Server?
Can you help me?
Thank you in advance.
SELECT
*
FROM
doTable
WHERE
doTableDate BETWEEN CONVERT (
datetime,
'03/01/2014 00:00:00',
103
)
AND CONVERT (
datetime,
'03/01/2014 00:00:00',
103
);
May 15, 2015 at 2:17 am
The second query will only return rows where the doTableDate is exactly 03/01/2014 00:00:00, because the upper and lower limits of the BETWEEN are the same
What do you want it to return? What does the data look like?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2015 at 10:25 am
Never use BETWEEN on dates or datetimes; instead, use >= and < the next date/time value.
Also, for literal dates, always use format 'YYYYMMDD', which is 100% unambiguous. '03/01/2014' could be Mar 1 or Jan 3, depending on where you are.
--examples::
--to get all rows for the jan 3 2014
WHERE
doTableDate >= '20140103' AND
doTableDate < '20140104'
--to get all rows for the month of feb 2014
WHERE
doTableDate >= '20140201' AND
doTableDate < '20140301'
--to get all rows for the hour of 8am on mar 12 2014
WHERE
doTableDate >= '20140312 08:00' AND
doTableDate < '20140312 09:00'
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".
May 15, 2015 at 2:39 pm
ScottPletcher (5/15/2015)
Never use BETWEEN on dates or datetimes;
Out of curiosity, why do you say this?
I could see it it you were talking about DATETIME data types only, simply because of the logical "<=" on the EndDateTime, but I don't see how it's an issue with the DATE data type...
May 15, 2015 at 2:44 pm
Jason A. Long (5/15/2015)
ScottPletcher (5/15/2015)
Never use BETWEEN on dates or datetimes;Out of curiosity, why do you say this?
I could see it it you were talking about DATETIME data types only, simply because of the logical "<=" on the EndDateTime, but I don't see how it's an issue with the DATE data type...
One reason I can come up with is that it is easy to calculate the start date of each month.
A second reason would be if the data type is changed from DATE to DATETIME you wouldn't have to change any code to keep it working correctly.
May 15, 2015 at 2:58 pm
Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...
May 15, 2015 at 3:02 pm
Jason A. Long (5/15/2015)
Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...
Some people are more adamant than others.
May 15, 2015 at 3:26 pm
The following is what I'd put in the NEVER do category...
WHERE CAST(t.SomeDateTime as DATE) BETWEEN @BegDate and @EndDate
Actually... I should say "Never do again" category... I have a few report procs that I still need to clean up... :unsure:
May 15, 2015 at 4:08 pm
Jason A. Long (5/15/2015)
The following is what I'd put in the NEVER do category...
WHERE CAST(t.SomeDateTime as DATE) BETWEEN @BegDate and @EndDate
Actually... I should say "Never do again" category... I have a few report procs that I still need to clean up... :unsure:
Just curious, why?
May 15, 2015 at 4:31 pm
Lynn Pettis (5/15/2015)
Jason A. Long (5/15/2015)
Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...Some people are more adamant than others.
Here's why you NEVER code it with BETWEEN:
what is the between code to get all of February, for say the current year?
Missing data when a column changes from datetime to datetime2 and for certain other data type changes.
Whereas the >= and < method works accurately in all those situations.
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".
May 15, 2015 at 5:21 pm
WOW... I could have sworn that the last time I tested, CASTing from a DATETIME to a DATE made it unsargable...
Just tetested with this...
DECLARE
@BegDate DATE = '2015-01-10',
@EndDate DATE = '2015-05-15'
SELECT
tt.DateAndTime
FROM dbo.TestTime tt
WHERE
tt.DateAndTime >= @BegDate
AND tt.DateAndTime < @EndDate
SELECT
tt.DateAndTime
FROM dbo.TestTime tt
WHERE
CAST(tt.DateAndTime AS DATE) BETWEEN @BegDate AND @EndDate
They both produced the exact same execution plan... I'm feeling like a moron... So I'll shut up now (and take it off the "never" list Monday morning)... :Whistling:
May 15, 2015 at 5:21 pm
ScottPletcher (5/15/2015)
Lynn Pettis (5/15/2015)
Jason A. Long (5/15/2015)
Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...Some people are more adamant than others.
Here's why you NEVER code it with BETWEEN:
what is the between code to get all of February, for say the current year?
Missing data when a column changes from datetime to datetime2 and for certain other data type changes.
Whereas the >= and < method works accurately in all those situations.
The problem is using the word NEVER. I understand and agree that for date ranges you should use DateColumn >= StartRange and DateColumn < EndRange.
I just won't use the term NEVER when expressing my opinion on this subject. It is like say never use cursors.
May 15, 2015 at 5:29 pm
Jason A. Long (5/15/2015)
WOW... I could have sworn that the last time I tested, CASTing from a DATETIME to a DATE made it unsargable...Just tetested with this...
DECLARE
@BegDate DATE = '2015-01-10',
@EndDate DATE = '2015-05-15'
SELECT
tt.DateAndTime
FROM dbo.TestTime tt
WHERE
tt.DateAndTime >= @BegDate
AND tt.DateAndTime < @EndDate
SELECT
tt.DateAndTime
FROM dbo.TestTime tt
WHERE
CAST(tt.DateAndTime AS DATE) BETWEEN @BegDate AND @EndDate
They both produced the exact same execution plan... I'm feeling like a moron... So I'll shut up now (and take it off the "never" list Monday morning)... :Whistling:
I would still refrain from using BETWEEN for date ranges. Biggest reason being if there is a data type change to column such as DATE to DATETIME or DATETIME to DATETIME2.
The reason I questioned your initial statement was for just the reason you discovered. Sometimes using a function does NOT mean the comparision becomes nonSARGable.
May 15, 2015 at 8:38 pm
Lynn Pettis (5/15/2015)
I would still refrain from using BETWEEN for date ranges. Biggest reason being if there is a data type change to column such as DATE to DATETIME or DATETIME to DATETIME2.
The reason I questioned your initial statement was for just the reason you discovered. Sometimes using a function does NOT mean the comparision becomes nonSARGable.
Oh I knew right away what you were driving at. That's why I did a retest after reading your comment.
That's that's the strange thing... I'd always worked with the assumption that casting a datetime to date didn't affect sargabilty... Then a couple weeks a ago, I came a cross a forum discussion (different forum) that made me 2nd guess that assumption so I did a few tests... Obviously the tests were flawed because I swear I saw the execution plan go from an index seek to an index scan. At this point I just wish I'd held on to the test code so that I figure out where I'd screwed up.
Anyway, thank you both for setting me straight.
May 15, 2015 at 9:45 pm
Jason A. Long (5/15/2015)
ScottPletcher (5/15/2015)
Never use BETWEEN on dates or datetimes;Out of curiosity, why do you say this?
I could see it it you were talking about DATETIME data types only, simply because of the logical "<=" on the EndDateTime, but I don't see how it's an issue with the DATE data type...
I can't speak for Scott but my answer would be, why do it two different ways when 1 way will work very well for both. I've also seen it when people finally make the realization of the huge mistake they made when they separated DATE and TIME into separate columns and recombine them further down the line. That, of course, means that they have to go back and find all of the BETWEEN code and change it to the >=/< code. It's much safer just to stay in that habit to begin with.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply