Change the Format of Input for a date from Y/M/D to D/M/Y in SQL/PHP from a form

Question

I need to be able to change the date format from Y/M/D to D/M/Y. The database with all the data that I am querying has the date set as Y/M/D whereas the input form has the input yet as D/M/Y.

Does anyone have any idea how I can change the format?

NB the Date is 'DOB'

Here is the SQL:

$sql="SELECT `country_name`,`gdp`,`population`,Cyclist.name,Cyclist.dob FROM Country JOIN Cyclist ON Country.ISO_id=Cyclist.ISO_id 
WHERE 'dob'
BETWEEN '".$date_1."' AND '".$date_2."'";

Here is the PHP for getting the data from the form submission

$date_1=$_REQUEST['date_1'];
$date_2=$_REQUEST['date_2'];

What is stopping you from parsing the input date any way you want it? — Tim Biegeleisen, May 08 '17 at 00:47
@TimBiegeleisen Are you talking about parsing the input from the source form or from the database? Unfortunately, we can't do that? We have to change the datatype somehow inside the code — RealHelper, May 08 '17 at 00:50
Why can't you just build a `Y-m-d` string inside your PHP code and use that in the query? — Tim Biegeleisen, May 08 '17 at 00:52
Please do **NOT** edit out your entire post after it has been answered. Accept one of the answers instead. — halfpastfour.am, May 22 '17 at 11:43

score 2 · Accepted Answer · answered May 08 '17 at 00:52

2

STR_TO_DATE parses a string to a date use a given format.

$sql="SELECT `country_name`,`gdp`,`population`,Cyclist.name,Cyclist.dob FROM Country JOIN Cyclist ON Country.ISO_id=Cyclist.ISO_id 
WHERE 'dob'
BETWEEN STR_TO_DATE('".$date_1.",'%d,%m,%Y') AND STR_TO_DATE('".$date_2.",'%d,%m,%Y')'";

STR_TO_DATE: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

answered May 08 '17 at 00:52

Raphael

1,760
1
12
21

Is there anyway to do it inside the PHP? Such as inside the REQUEST – RealHelper May 09 '17 at 15:35
Yes, you could do like Pradnya suggested: $date_1=date('d-m-Y', strtotime($_REQUEST['date_1'])); $date_2=date('d-m-Y', strtotime($_REQUEST['date_2'])); – Raphael May 09 '17 at 15:39
Thank you! Hopefully it has worked Getting issue with output `$res=& $db->query($sql); if(PEAR::isError($res)){ die($res->getMessage()); } $InputDateArray=array(); echo 'hello'; while($row=$res->fetchRow()) $InputDateArray[] = $row; echo json_encode($InputDateArray);` – RealHelper May 09 '17 at 15:50
Sorry, did it work or did something else went wrong? – Raphael May 09 '17 at 15:54
I think something else has gone wrong with the output – RealHelper May 09 '17 at 15:56
check the format of the date that is set to $date_2 and $date_1 – Raphael May 09 '17 at 16:04

score 0 · Answer 2 · answered May 08 '17 at 00:55

0

You may change the date format in php date() and strtotime() e.g: $date_new = date('d-m-Y', strtotime($date)); and then passing it in sql query.

answered May 08 '17 at 00:55

Pradnya Pathare

1
2

Change the Format of Input for a date from Y/M/D to D/M/Y in SQL/PHP from a form

2 Answers2