January 19, 2015 at 1:19 pm
Good afternoon all,
I have the query below and it returns zero but if I remove the first part of the WHERE clause, it works using the second half parameters. It also works if I search in 2014 only. The date is inserted by getDate(). Is there a reason I cannot get data?
select count(distinct c.sn) as total_prod
from completedUnit c
join assignworkorder p
on c.workorder = p.workorder
join tlkp_item i
on p.item = i.item
where (MONTH(c.dateentered) >= 11 and DAY(c.dateentered) >= 23 and YEAR(c.dateentered) >= 2014)
and (MONTH(c.dateentered) <= 01 and DAY(c.dateentered) <= 19 and YEAR(c.dateentered) <= 2015)
Thanks,
kabaari
January 19, 2015 at 1:28 pm
You linked two Boolean expressions together with AND, meaning both must return TRUE in order for the WHERE clause to return TRUE.
However, looking at the first parts already leads to contradictions.
MONTH(c.dateentered) >= 11
and
MONTH(c.dateentered) <= 01
If the first one is true, how can the second one ever be true? It's just not possible.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 19, 2015 at 1:31 pm
Koen Verbeeck (1/19/2015)
You linked two Boolean expressions together with AND, meaning both must return TRUE in order for the WHERE clause to return TRUE.However, looking at the first parts already leads to contradictions.
MONTH(c.dateentered) >= 11and
MONTH(c.dateentered) <= 01If the first one is true, how can the second one ever be true? It's just not possible.
The same thing applies to the Day.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 19, 2015 at 1:32 pm
I believe that query won't work because your WHERE clause is quite contradictory.
Basically, you're looking for where the MONTH datepart item is both >= 11 and <= 1; mathematically, that's not going to work. Ditto for the DAY datepart; it's looking for something >= 23 and <= 19, which won't work.
The best means of doing this would be to just use the full date; for example:
WHERE c.DateEntered >= '2014-11-23' AND c.DateEntered < '2015-01-20'
Is there something preventing the use of a date search like that? It would be the cleanest way of doing it; note the ending date's notation, as it does a less-than search, it doesn't include the ending date, but it will include everything up to the precision of your data type to the last moment on 2015-01-19.
Also, by calling the individual date functions on the DateEntered column, you're keeping your query from using any indexes that might be on that column, since the function has to process each value to find the ones that match your condition. Doing a conventional date search would improve performance quite a bit, assuming proper index structure.
- 😀
January 19, 2015 at 1:33 pm
Alvin Ramard (1/19/2015)
Koen Verbeeck (1/19/2015)
You linked two Boolean expressions together with AND, meaning both must return TRUE in order for the WHERE clause to return TRUE.However, looking at the first parts already leads to contradictions.
MONTH(c.dateentered) >= 11and
MONTH(c.dateentered) <= 01If the first one is true, how can the second one ever be true? It's just not possible.
The same thing applies to the Day.
I know, I was just too lazy to copy paste that too 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 19, 2015 at 1:34 pm
kabaari (1/19/2015)
Good afternoon all,I have the query below and it returns zero but if I remove the first part of the WHERE clause, it works using the second half parameters. It also works if I search in 2014 only. The date is inserted by getDate(). Is there a reason I cannot get data?
select count(distinct c.sn) as total_prod
from completedUnit c
join assignworkorder p
on c.workorder = p.workorder
join tlkp_item i
on p.item = i.item
where (MONTH(c.dateentered) >= 11 and DAY(c.dateentered) >= 23 and YEAR(c.dateentered) >= 2014)
and (MONTH(c.dateentered) <= 01 and DAY(c.dateentered) <= 19 and YEAR(c.dateentered) <= 2015)
Thanks,
kabaari
I'm guessing the WHERE clause you want would would work better as:
WHERE CAST(c.dateentered AS DATE) BETWEEN '2014-11-23' AND '2015-01-19'
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 19, 2015 at 1:38 pm
You have two impossible combinations in your selection criteria:
MONTH(c.dateentered) >= 11 and (MONTH(c.dateentered) <= 01
DAY(c.dateentered) >= 23 and DAY(c.dateentered) <= 19
It appears you are looking for data from a date range, so you probably want something like this:
where
c.dateentered >= '20141123' and
c.dateentered < '20150120'
January 19, 2015 at 1:40 pm
Alvin and Andrew,
Those both achieved my goal. I was having difficulty with querying against a getDate() Inserted value. The time is also captured at insert which had thrown some of my queries into a tizzy. Thanks!
WHERE CAST(c.dateentered AS DATETIME) BETWEEN '2014-11-23' AND '2015-01-19'
January 19, 2015 at 5:33 pm
kabaari (1/19/2015)
Alvin and Andrew,Those both achieved my goal. I was having difficulty with querying against a getDate() Inserted value. The time is also captured at insert which had thrown some of my queries into a tizzy. Thanks!
WHERE CAST(c.dateentered AS DATETIME) BETWEEN '2014-11-23' AND '2015-01-19'
What is the datatype of the "dateentered" column in the actual table?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2015 at 7:24 am
kabaari (1/19/2015)
Alvin and Andrew,Those both achieved my goal. I was having difficulty with querying against a getDate() Inserted value. The time is also captured at insert which had thrown some of my queries into a tizzy. Thanks!
WHERE CAST(c.dateentered AS DATETIME) BETWEEN '2014-11-23' AND '2015-01-19'
Casting to DateTime instead of date will cause you problems with c.dateentered having values during the day on 2015-01-19 and has a value of 2015-01-09 01:02:03, for example.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 20, 2015 at 2:08 pm
Alvin Ramard (1/20/2015)
kabaari (1/19/2015)
Alvin and Andrew,Those both achieved my goal. I was having difficulty with querying against a getDate() Inserted value. The time is also captured at insert which had thrown some of my queries into a tizzy. Thanks!
WHERE CAST(c.dateentered AS DATETIME) BETWEEN '2014-11-23' AND '2015-01-19'
Casting to DateTime instead of date will cause you problems with c.dateentered having values during the day on 2015-01-19 and has a value of 2015-01-09 01:02:03, for example.
Casting dateentered to DATE or DATETIME will make the query non-SARGable, meaning it cannot use a index for anytime except an index scan. Also, DATE was not a valid datatype in SQL Server 2005.
Also, dates in the format '2014-11-23' and '2015-01-19' are not in an unambiguous format, meaning they can produce unexpected values or conversion errors depending on the setting of DATEFORMAT. I prefer using the unambiguous date format YYYYMMDD.
set dateformat ydm
select T1 = convert(datetime,'20141123')
select T2 = convert(datetime,'2014-11-23')
Results:
T1
-----------------------
2014-11-23 00:00:00.000
T2
-----------------------
Msg 242, Level 16, State 3, Line 3
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Assuming that the column dateentered is DATETIME (or even DATE or DATETIME2), the following selection criteria of greater than or equal to start date and less then the day after the end date using the unambiguous date format YYYYMMDD is probably the easiest way to get a SARGable query.
where
c.dateentered >= '20141123' and
c.dateentered < '20150120'
January 28, 2015 at 1:55 pm
Jeff,
It's DATETIME.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply