[SOLVED] Date problem

[Mr Chris]

Hi Guys,

 

On my site I have a Javascript calendar which picks up the date in a YYYY-MM-DD format and searches my database for a story for the date picked.

 

Now when this date is selected I want it to return all results where the date is less or equal to now().

 

And using this query the system worked:

 

$sql = "SELECT  * FROM cms_stories WHERE section LIKE '%$section%' AND published_web_date LIKE '%$published_web_date%' AND published_web_date <= NOW()";

 

However, I’ve now updated my database so that the field published_web_date is in the type of a timestamp – rather than a date as it was before.

 

So how can I change the query above to ask it do the same thing, but now it searching on a timestamp rather than a date field?

 

Thanks

 

Chris 

[mmarif4u]

u can try date_format method to extract the data in ur query.

[Mr Chris]

Thanks, but how does that work?

[mmarif4u]

Can u show me the date and time stored in ur db , an example.

[Mr Chris]

Yep.  Thank you.  It is in this type of format:

 

2007-04-26 16:24:27

(timestamp)

[mmarif4u]

Yep.  Thank you.  It is in this type of format:

 

2007-04-26 16:24:27

(timestamp)

 

 

$sql = "SELECT  * FROM cms_stories WHERE section LIKE '%$section%' AND DATE_FORMAT(published_web_date,'%Y-%m-%d') LIKE '%$published_web_date%' AND published_web_date <= NOW()";

 

Try and let me know it works or not.

[Mr Chris]

Yep, works a treat.  Many Thanks.  Could not explain how it works can you for my future reference?

[mmarif4u]

Just take a look to ur date in db, it is in the format of 2007-04-26 time,

Date_format() is to extract date in the form of what u wish (in the function)

DATE_FORMAT(date_publish,'%Y-%m-%d')

%Y will show year in four digits like 2007,2000,1995

%m will show the month in two digits like 01,08,12

%d will show the day in two digits like 05,15,20

 

Now take a look on ur date format in db it is 2007-04-26.

Other thing is - ,in ur date u save it as 2007-04-26 not as 2007/04/26.

 

So the DATE_FORMAT(date_publish,'%Y-%m-%d')

 

Hope this will help u.

[Mr Chris]

Thanks.  Explains it brilliantly!