Millica Millica - 10 months ago 33
MySQL Question

Trying to make a stored Datetime value shorter with SQL Convert function

I'm new to using the

function. I'm trying to make a datetime value from my table shorter. Currently my datetime values look something like this,
2016-10-14 16:51:41
, but I'd like to make it look something like mm/dd/yy.

I don't know if this is the right approach (must not be since it doesn't work), but I've generated a query using the
function and then fetching the data with a

Here's the code I'm using:

$sql = "SELECT id, time, CONVERT(VARCHAR(11), time) as something FROM tableName";
$query = mysqli_query($db, $sql);
$statusnumrows = mysqli_num_rows($query);

// Gather data about parent pm's
if($statusnumrows > 0){
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
$time= $row["time"];

is the name of the column which has the datetime type.

Thanks in advance, for suggestions/advice.

Answer Source

You should store datetime in your DB in proper data types like datetime etc.

As I can see you are using PHP, if you need to OUTPUT this datetime in some interface (f.e. HTML page), you should use PHP functions to convert date in format you need. That is the good practice

DB should STORE the data but formatting is front-end problem.

Simple example of strtotime() and date():

$Date = "2016-10-15";
$newDate = date("m/d/Y", strtotime($Date));

You can read docs on the PHP site: strtotime and date