Date & Time format in database.

[ubuntu]

Dear all,

 

I'm using MySQL database, the problem is I've setup that user input the Date format in dd/mm/yyyy (example: 14may2007) and Time (example: 12:00pm). How do I convert it to Date & Time format in database format before insert into database? Sorry, noob here. Any advice or suggestion are most welcome. Thank you, regards.

[jitesh]

dd/mm/yyyy (example: 14may2007) ??????

mention exact date format.

[ubuntu]

To simplify what I mean, below is the exact data.

 

 

The time is "12:00pm", including pm with it. Then how do I insert these two values into databases? I keep getting errors when inserting.

[jitesh]

<?php
$date_format = "23feb2007";
$time_format = "12:00pm";

//-------------------------------------------------- End for Time

$time = substr($time_format,0,5);
$format = substr($time_format,5,2);
if($format == 'pm'){
   if($time == "12:00"){
   		$new_formated_time = "12:00"; 
   }else{
   $time_arr = explode(":",$time);
   $t_time = 12 +  $time_arr[0];
   $new_formated_time =  $t_time .":".$time_arr[1];
    }
}else{
if($time == "12:00"){
	$new_formated_time = "00:00"; 
}else{
   $new_formated_time =   $time;
}
}

$time_l = explode(":",$new_formated_time);
$hour_l = $time_l[0];
$mnt_l = $time_l[1];
// ------------------------------------------------ For Date
$date_l = substr($date_format,0,2);
$month_l = substr($date_format,2,3);
$year_l = substr($date_format,5,4);
$months = array("jan"=>"01", "feb"=>"02", "mar"=>"03", "apr"=>"04", "may"=>"05", "jun"=>"06","jul"=>"07", 
		"aug"=>"08", "sep"=>"09", "oct"=>"10", "nov"=>"11", "dec"=>"12");


$final_date = date("Y-m-d H:i:s",mktime($hour_l,$mnt_l,0,$months[$month_l],$date_l,$year_l));

//------------------------ This is o/p
echo $final_date;
?>

[ubuntu]

Thanks bro, but can you show me a sample as I still getting error can't insert into database. This is my query:

 

I have a table names perjalanan. With field Date_of_Depart (Date) & Time_of_Depart (Time).

Inserting data command:

insert into perjalanan values (14may2007, 12:00);

 

I think mostly is the way I insert data wrong right?

[jitesh]

If you want to store date and time as a string

Then Keep  Date_of_Depart - Varchar

                Time_of_Depart - Varchar

 

insert into perjalanan values ('14may2007', '12:00');