If Date Is More Than Todays Date

[dreampho]

Hi.

 

I have a database field (col_id_3) with a dat in Ymd format (YYYYMMDD).

 

I am trying to have the query only display results that have the 'col_id_3' field with a date after todays date.

 

The code below isnt working, can anyone help?

 

AND matrix.col_id_3 > (DATE_FORMAT(NOW(), 'Ymd'))

 

Thank you

[kicken]

I have a database field (col_id_3) with a dat in Ymd format (YYYYMMDD).

 

Is this a VARCHAR field and not a DATE field? I'm going to assume so based on the way the question is written. If it is a date field though you could just compare directly to NOW().

 

AND matrix.col_id_3 > (DATE_FORMAT(NOW(), 'Ymd'))

 

The DATE_FORMAT field is not like PHP's date function. It takes %-style identifiers for the different part of the dates. The proper way to write that line would be like so:

AND matrix.col_id_3 > (DATE_FORMAT(NOW(), '%Y%m%d'))

 

[dreampho]

Hi. Thanks for getting back to me.

 

The field with the date is set as TEXT.

 

I have tried the code wtith % and it still isnt working. Is there something else I could use?

[Jessica]

SELECT (DATE_FORMAT(NOW(), '%Y%m%d')) FROM tbl

 

What does it show?

[PFMaBiSmAd]

Posting the entire query that 'doesn't work' would help, along with a sample of the data you have in your matrix.col_id_3 column that you expect the query to match.

[dreampho]

Thanks for your replys.

 

jesirose - I am not quite sure where I would need to do this, in phpmyadmin?

 

Here is the entire query:

 

SELECT titles.entry_id, titles.title, matrix.entry_id, data.field_id_11 as tour_description, data.field_id_13 as ref, data.field_id_8 as tour_price, titles.url_title, titles.status, assets_entries.asset_id, matrix.col_id_3 as start_date, matrix.col_id_4 as end_date, data.field_id_157 as inventory, data.field_id_158 as last_places_available_limit, SUBSTR( assets.file_path, 12 ) as file_path, matrix.col_id_3
FROM exp_channel_titles as titles
LEFT JOIN exp_channel_data AS data ON data.entry_id = titles.entry_id
LEFT JOIN exp_assets_entries AS assets_entries ON assets_entries.entry_id = data.entry_id
LEFT JOIN exp_assets AS assets ON assets_entries.asset_id = assets.asset_id
LEFT JOIN exp_matrix_data AS matrix ON matrix.entry_id = data.entry_id
WHERE titles.channel_id = '4'
AND titles.status = 'Active'
AND assets_entries.field_id = '84'
AND matrix.col_id_3 > (DATE_FORMAT(NOW(), '%Y%m%d'))
AND MONTH(matrix.col_id_3) = '{segment_4}'   
OR MONTH(matrix.col_id_4) = '{segment_4}'   
GROUP BY titles.entry_id
ORDER BY matrix.col_id_3 ASC

 

A sample of the data in cold_id_3 is : 20120903

 

Thank you

[PFMaBiSmAd]

If the sample data you posted is what you expect the query to match, that date is not greater-than today, so that query should not return any rows.

 

Edit: Also, your use of the OR keyword in the query will match everything with the MONTH(matrix.col_id_4) = '{segment_4}' value. You need to use () to force the operator precedence.

 

Also, also, you should not have column names like col_id_4, ... Those don't indicate the meaning of the data and while they might have meaning for you today, they probably won't for the next person who needs to work on your code or to anyone here who would like to help.

Edited September 22, 2012 by PFMaBiSmAd

[dreampho]

Sorry, I thought you meant the format, the data would be after todays date to display a result so:

 

20120930 or 20130112

[PFMaBiSmAd]

Sorry to pick, but programming is an exact science and we only see the information you supply in your posts. Someone asked an exact question for a reason (we get people trying to match all kinds of nonsense data and we only uncover the exact problem once we see the problematic data) -

Posting the entire query that 'doesn't work' would help, along with a sample of the data you have in your matrix.col_id_3 column that you expect the query to match.

[dreampho]

Sorry, I misread your post.

 

The problem is not that it is returning nothing, its that it is returning everything even if its before the date and I am completey stumped as to why. Any further help would be fantastic.

[PFMaBiSmAd]

I added some information to my post above, probably while you were replying -

 

Edit: Also, your use of the OR keyword in the query will match everything with the MONTH(matrix.col_id_4) = '{segment_4}' value. You need to use () to force the operator precedence.

[Jessica]

Honestly you should convert the fields to DATE format.

[dreampho]

I tried with the fields set to 'date' and it didnt work either.

 

The field names are not chosen by me, I am working through a content management system that dicatates this.

 

Does this mean to say that nobody can see any reason why the below doesnt work?

 

AND matrix.col_id_3 > (DATE_FORMAT(NOW(), '%Y%m%d'))

[kicken]

Does this mean to say that nobody can see any reason why the below doesnt work?

Did you fix the query by adding parenthesis around your condidtions, as stated by PFMaBiSmAd?

 

The way your WHERE is written if will be interepreted as:

WHERE 
(titles.channel_id = '4' AND titles.status = 'Active' AND assets_entries.field_id = '84' AND matrix.col_id_3 > (DATE_FORMAT(NOW(), '%Y%m%d')) AND MONTH(matrix.col_id_3) = '{segment_4}')
OR (MONTH(matrix.col_id_4) = '{segment_4}')

 

In other words, either all those and conditions must be true, or the single condition on the end must be true in order to select a row. So you'll pull any rows where MONTH(matrix.col_id_4) = '{segment_4}' regardless of the state of all your other conditionals.

 

I am assuming you want the two MONTH() conditions to be grouped together, in which case you have to enclose them both in a set of parenthesis:

WHERE 
titles.channel_id = '4' 
AND titles.status = 'Active' 
AND assets_entries.field_id = '84' 
AND matrix.col_id_3 > (DATE_FORMAT(NOW(), '%Y%m%d')) 
AND 
  (MONTH(matrix.col_id_3) = '{segment_4}' OR MONTH(matrix.col_id_4) = '{segment_4}')

 

Written that way, then every row must match the first four conditions, as well as one of the two possible MONTH based conditions.

 

[dreampho]

Thank you kicken for explaining this, and PFMaBiSmAd for your help! This was the issue, I am slowly getting to grips with this... I am very greatful for your help!