CODEWITHSUNDEEP
phpmysql
Michelle
Michelle

Reputation: 570

Convert variables and strings to datetime format

I currently have the following variables and formatted data:

I need to combine them into one variable 'schedstart' of type 'datetime' to insert into my database, and I'm completely lost. I have tried to research the issues, and found suggestions to use mktime() or sttodate() but I couldn't find any reliable syntax guidelines.

How do I combine those variables using PHP?

Upvotes: 0

Views: 645

Answers (3)

mpen
mpen

Reputation: 282825

I don't know why on earth you'd want to do this in SQL when it's super easy to do in PHP.

(It's usually best to put as little strain as possible on the SQL server as that's usually the bottle-neck, plus, it's much uglier to piece together using CONCAT in SQL)

MySQL dates are essentially just strings formatted in a particular way. e.g. 2008-12-31 23:59:59

So just take your different date pieces and stuff them together so they look like that, and then use it in your SQL query as you please (put it in single quotes).

see MySQL docs for more details.

Easiest way might look like this:

$mysql_formatted_date = date('Y-m-d H:i:s', strtotime("Scheduling_StartDate_Year-$Scheduling_StartDate_Month-$Scheduling_StartDate_Day $Scheduling_StartTime"));

i.e., let strtotime parse out your date and handle converting am/pm to 24-hour time, then use date to force it into the correct format.

mysql_query("SELECT '$mysql_formatted_date' ...");

Upvotes: 0

Mateus Schneiders
Mateus Schneiders

Reputation: 4903

You can consider concatenating in a proper format on the PHP:

<?php

$dateString = $Scheduling_StartDate_Month."/".$Scheduling_StartDate_Day."/".$Scheduling_StartDate_Year." ".$Scheduling_StartTime;
$result = mysql_query("SELECT STR_TO_DATE('".$dateString."', '%m/%d/%Y %h:%i %p')");

?>

Upvotes: 1

Taryn
Taryn

Reputation: 247650

You can use the following which use the STR_TO_DATE() and CONCAT() functions:

select 
  str_to_date(
    concat(Scheduling_StartDate_Year,'-',
          Scheduling_StartDate_Month, '-',
          Scheduling_StartDate_Day, ' ',
          Scheduling_StartTime), '%Y-%m-%d %h:%i') as yourDate
from yourtable

See SQL Fiddle With Demo

Results:

yourDate
2012-10-01 03:00:00

Upvotes: 3

Related Questions