Jump to content

Converting Date Into MySQL Format


FinalRevival

Recommended Posts

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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.