CODEWITHSUNDEEP
mysqlstringvalidationdate
whispers
whispers

Reputation: 962

MySQL, how validate against string dates using PHP?

I am working with a table that has existing data in it..

The 'date' column has its data like so:

Saturday, September 26, 2015 Saturday, September 26, 2015 Saturday, September 27, 2015 Saturday, September 27, 2015

etc..etc..

Using PHP (or maybe just straight MySQL?) how can I only pull/list records (data) that are BEFORE a certain date (todays date)

Something like this.. but with the way the 'date' (cough) is in the3 table. I'm not sure how to handle it properly.

SELECT s.sessioncode, s.sessionname, s.sessiontime FROM fc15_sessions AS s  WHERE s.sessiondate < **TODAYS_DATE** ORDER BY s.id

Testing with some PHP and messing with that 'date' format...

This seems to work and be something I can work with... but not sure how I put it together in a validation:

$string = 'Saturday, September 26, 2015';
$timestamp = strtotime($string);

echo "DATE CHECK: " . date("Y-m-d H:i:s", $timestamp);

MySQL Attempt:

SELECT s.id, s.sessiondate, s.sessioncode, s.sessionname, s.sessiontime
FROM fc15_sessions AS s
WHERE STR_TO_DATE(s.sessiondate, '%m/%d/%Y' ) > date( "m/d/Y" )
ORDER BY s.id

Upvotes: 0

Views: 110

Answers (2)

P. Sarrapochiello
P. Sarrapochiello

Reputation: 7

You can use this MySQL functions:

  • STR_TO_DATE( string, format )
  • DATE_FORMAT( date, format )
  • CURDATE() or equivalently CURRENT_DATE()

So your final MySQL query becames:

SELECT s.sessioncode, s.sessionname, s.sessiontime  
FROM fc15_sessions AS s  
WHERE DATE_FORMAT(STR_TO_DATE(s.sessiondate, '%W, %M %d, %Y'),"%Y-%m-%d") < CURDATE()
ORDER BY s.id

NOTE: You must use the format "%Y%m%d" or "%Y-%m-%d" (i.e. YYYY-MM-DD with or without separator) for correct date string compare.
In this case you must use the format "%Y-%m-%d" as the function CURDATE() (or equivalently CURRENT_DATE()) returns the results in this format.

Upvotes: 1

whispers
whispers

Reputation: 962

Ultimately.. in the end (and against all the comments it cant be done)..

This is what worked for me in the end. (Properly using the STR_TO_DATE params, I had it wrong, its how the data currently is formatted, not HOW you want it formatted)

SELECT DATE_FORMAT(STR_TO_DATE(targetColumn, '%W, %M %d, %Y'),'%m/%d/%Y') FROM tableName;

Upvotes: 0

Related Questions