Jump to content


Photo

Converting Date Into MySQL Format


  • Please log in to reply
4 replies to this topic

#1 FinalRevival

FinalRevival
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 12 June 2006 - 02:02 AM

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.

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;


#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 12 June 2006 - 02:28 AM

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?
Legend has it that reading the manual never killed anyone.
My site

#3 FinalRevival

FinalRevival
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 12 June 2006 - 03:24 AM

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:

$fdate='06-31-2004';

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

the result will be : 2004-06-31

#4 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 12 June 2006 - 04:33 AM

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():

<?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;

?>

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] INTO dates (date) VALUES (FROM_UNIXTIME(599472000))
[span style=\'color:blue;font-weight:bold\']INSERT[/span] INTO dates (date) VALUES ('1988[span style=\'color:orange\']-12-30'[/span]) [!--sql2--][/div][!--sql3--]

They both inserted a row with date 19881230000000
~ D Kuang

#5 FinalRevival

FinalRevival
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 12 June 2006 - 04:52 AM

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

Thank you very much!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users