date format

[techker]

Hey guys i insert my date as:01/14/2013 so m-d-y

 

 

how can i make it so that the mysql recognizes my dates:

 

i got this but it doesnt work..

 

$today = date('m-d-Y');
$d1 = 'SELECT * FROM '. $row5['Fiscale_Sortie'] .' WHERE DATE(Date_Rempli)  = "$today"'; 
$result8 = mysql_query($d1) or die(mysql_error()); 
$total_rows8 = mysql_fetch_row($result8);

[Barand]

What format is

Date_Rempli

[techker]

The field were i store the date

[requinix]

But what format is it? What type of column? DATE? DATETIME? VARCHAR?

 

MySQL uses Y-M-D so try with your $today formatted that way.

[techker]

ya but my error was to take over somebody else's work and they insert into varchar M-D-Y and there's like already over 400 entries...

[Barand]

You cannot use DATE(), or most other mysql datetime functions on your field as it is. If it is a string in m-d-y format then = '01-15-2012', for example should work. Equal to is about the only comparison that will.

 

You can use STR_TO_DATE to format as DATE type

[TRI0N]

You will need to explode your m-d-Y to Y-m-d before inserting or updating into your database.

 

$my_date_format = '1-15-2013' ;
$date_fix = explode('-', $my_date_format) ;
$new_month = $date_fix[0] ;
$new_day = $date_fix[1] ;
$new_year = $date_fix[2] ;
$new_date_to_store = $new_year.'-'.$new_month.'-'.$new_day ;

 

$new_date_to_store is the date format you will want to store into your database.

 

Then you will need to do the exact same thing to reverse the date upon SELECT to parse it to the format you want to see when viewed on a page.

 

$db_date_format = $row['date'] ;
$date_fix = explode('-', $db_date_format) ;
$new_year = $date_fix[0] ;
$new_month = $date_fix[1] ;
$new_day = $date_fix[2] ;
$new_date_to_display = $new_month.'-'.$new_day.'-'.$new_year ;
echo $new_date_to_display ;

Edited January 15, 2013 by TRI0N

[TRI0N]

The only problem that storing dates in VARCHAR is that PHP Date functions have more flexibilities when you wish to short or calculate dates in the correct date format. If you do this those functions will not work like they should if the date is not in its standard format.

[TRI0N]

I just noticed your original date format is 1/15/2013

 

The correct code to convert:

$my_date_format = '1/15/2013' ;
$date_fix = explode('/', $my_date_format) ;
$new_month = $date_fix[0] ;
$new_day = $date_fix[1] ;
$new_year = $date_fix[2] ;
$new_date_to_store = $new_year.'-'.$new_month.'-'.$new_day ;

 

The correct code to display:

$db_date_format = $row['date'] ;
$date_fix = explode('-', $db_date_format) ;
$new_year = $date_fix[0] ;
$new_month = $date_fix[1] ;
$new_day = $date_fix[2] ;
$new_date_to_display = $new_month.'/'.$new_day.'/'.$new_year ;
echo $new_date_to_display ;

 

That will do the trick!

[Barand]

You could update your dates with a single line of mysql

 

UPDATE mytable SET datefield = STR_TO_DATE(datefield, '%e-%m-%Y')[code]

 

then change the type to DATE

[Christian F.]

Also, to properly convert the MySQL timestamp to another format (for displaying) you can either use the built-in functions for MySQL, or the DateTime class in PHP.

[techker]

You could update your dates with a single line of mysql

 

UPDATE mytable SET datefield = STR_TO_DATE(datefield, '%e-%m-%Y')[code]

 

then change the type to DATE

 

thx for the help guys.

this will take the date a convert it?