INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

between date range ignoring 0000-00-00

between date range ignoring 0000-00-00

(OP)
Hi

I’ve been successfully using the code on thread436-1721678: find products not listed (closed thread) since the answer was given, but have run in to a problem now that the date field has changed.

CODE

SELECT u . * , r.c
FROM products p
LEFT OUTER JOIN (
SELECT productid, COUNT( reviewid ) AS c
FROM reviews
WHERE (reviewdate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
GROUP BY productid
)r ON r.productid = p.productid
WHERE (r.c IS NULL || r.c =0)
AND manu = ‘xyz'
GROUP BY p.productid 

I have added an extra date column after reviewdate for authoriseddate.

The powers that be want to see a report that shows the same report as above but based on authoriseddate, not reviewdate.

The problem is that when I added the new column, all existing records placed 0000-00-00 into the authoriseddate column, only new records have a real date in this field.

Running the report above replacing authoriseddate with reviewdate is also returning the results where authoriseddate = 0000-00-00 i.e. all of the results before this new field was added.

I tried to add a new clause to exclude the early comments

CODE

SELECT u . * , r.c
FROM products p
LEFT OUTER JOIN (
SELECT productid, authoriseddate, COUNT( reviewid ) AS c
FROM reviews
WHERE (authoriseddate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
AND authoriseddate <> ‘0000-00-00’
GROUP BY productid
)r ON r.productid = p.productid
WHERE (r.c IS NULL || r.c =0)
AND manu = ‘xyz'
GROUP BY p.productid 

But this had no effect on the results returned, also in the results the authoriseddate column is showing NULL.

Can anyone help?

RE: between date range ignoring 0000-00-00

(OP)
I have tried using just the join part

CODE

SELECT productid, COUNT( reviewid ) AS c
FROM reviews
WHERE (reviewdate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
GROUP BY productid 

and this ignores the 0000-00-00 values on its own, so why when using it as a whole is the resultset returning 0000-00-00 rows? aarrghhh

RE: between date range ignoring 0000-00-00

this doesn't look right

CODE

SELECT productid, authoriseddate, COUNT( reviewid ) AS c
FROM reviews
WHERE (authoriseddate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
AND authoriseddate <> ‘0000-00-00’
GROUP BY productid 
if you are grouping by productID that presupposes that all products will be authorised on the same date.

i wonder whether you mean this

CODE

SELECT productid, COUNT( reviewid ) AS c
FROM reviews
GROUP BY productid
HAVING (authoriseddate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) ) 

in total

CODE

SELECT              u.*, r.c
FROM                products p
LEFT OUTER JOIN (

     SELECT         productid, COUNT( reviewid ) AS c
     FROM           reviews
     GROUP BY       productid
     HAVING         (authoriseddate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
) AS r 
ON                  r.productid = p.productid
WHERE               (r.c IS NULL || r.c = 0)
                    AND u.manu = 'xyz'
GROUP BY            p.productid 

RE: between date range ignoring 0000-00-00

why convert to date?

You're not doing any date calculation just between one string and another, were the string is in the right structure to do a between or a collation, it seems to me. That way the string 0000-00-00 never qualifies in the between nor does it need any other manipulation to avoid a conversion error.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: between date range ignoring 0000-00-00

i always cast strings to date when used in date calculations. i recall spending days trying to debug some wordpress core problems when some versions of mysql made wrong implicit castings. seems safest to do it properly.


anyway I suspect the nub of the OP's problem is that the query contained an unnecessary field and was using a where clause rather than a having clause.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close