Converting Date Into MySQL Format

[FinalRevival]

My first code was working fine. It is MySQL 5.x installed on my machine. I used STR_TO_DATE() then uploaded my files, found out that the server has the version 3.x.x which doesn't support the function I used. Spending 6 hours today I found SUBSTRING_INDEX() function and how to convert a date from MM-DD-YYYY to YYYY-MM-DD format.

From the command line everything works fine. I've tested the function but couldn't use it with PHP. I wrote a code to test the function with PHP and I am stuck, I don't know what I am doing wrong. I copied the code below.

If there is any other way that I could convert the date format, can you tell me about that? I appreciate any help. Thank you and here is the code used to test the function. Page doesn't give any error messages, nothing, just a blank white page is all it displayed.

[code]
mysql_connect($hostname,$username,$password);
mysql_select_db($database) or die(mysql_error());

#$fdate='12-31-2006';

#echo $fdate;

$query="SUBSTRING_INDEX('12-31-2006','-',-1)";

$result = mysql_query($query);

echo $result;
[/code]

[AndyB]

I don't understand what you're asking. Are you looking for some MySQL query you can run on a database table, or some just a couple of lines of php to convert one date format to another

Do you have an existing database with date information which you want to transform into another date format (can't think why)? Do you have an existing database with date information but want to display the date drawn from the database in a different format?

[FinalRevival]

Hi AndyB, Thank you for your reply.

There is a form I get the dates from. The format I recieve the dates is MM-DD-YYYY. I had to change the format to YYYY-MM-DD so I could insert into mysql db.

I solved this problem by using a PHP function, which is below.

You see, MySQL versions didn't match so I had to look for other ways of changing date format.

I believe there is other ways of doing this. You are still very welcome to share those solutions. I want to learn different ways of doing things.

The way I solved this date format issue:

[code]
$fdate='06-31-2004';

function changeDate ($inputDate) {
  $split = explode("-", $inputDate);
  $outputDate = "" . $split[2] . "-" . $split[1] . "-" . $split[0];
  Return $outputDate;
}
echo changeDate ($fdate);
[/code]

the result will be : 2004-06-31

[poirot]

Well, you can always use PHP's strtotime. It will give you a UNIX timestamp of the date. But I don't like the idea of using it because some dates may be ambiguous so I'll use a bunch of substr():

[code]<?php

$str = '12-30-1988';

$unix = mktime(0, 0, 0, substr($str, 0, 2), substr($str, 3, 2), substr($str, 6, 4));
$ready = date("Y-m-d", $unix);

echo $unix . '<br />';
echo $ready;

?>[/code]

UNIX: 599472000
READY: 1988-12-30

Then you can insert the $ready timestamp directly or use FROM_UNIXTIME($unix):

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']INSERT[/span] [color=green]INTO[/color] [color=orange]dates[/color] (date) VALUES (FROM_UNIXTIME(599472000))
[span style=\'color:blue;font-weight:bold\']INSERT[/span] [color=green]INTO[/color] [color=orange]dates[/color] (date) VALUES ([color=red]'1988[span style=\'color:orange\']-[/color]12[color=orange]-[/color]30'[/span]) [!--sql2--][/div][!--sql3--]

They both inserted a row with date 19881230000000

[FinalRevival]

That is another good way to do this. I didn't know about unix time.

Thank you very much!