0

I want to get date from VARCHAR column.

(eg: 4/14/2018 12:00:00 AM)

How do I display only date

(eg: 4/14/2018)?

SELECT date(created_at) from self_balance

here created_at(varchar)

this returns NULL value

Rahul
  • 18,271
  • 7
  • 41
  • 60
  • 1
    If it's a date / datetime, why is the column a varchar? – Jonnix May 30 '19 at 09:24
  • because i could not insert AM/PM to a datetime column –  May 30 '19 at 09:26
  • So? The DB uses 24hr time. If you want AM / PM in output, you can output whatever format you want after retrieving it. – Jonnix May 30 '19 at 09:29
  • 1
    use STR_TO_DATE like: SELECT STR_TO_DATE('4/14/2018 12:00:00AM','%m/%d/%Y') from dual – Nguyên Ngô Duy May 30 '19 at 09:29
  • Possible duplicate of [Parse date in MySQL](https://stackoverflow.com/questions/3296725/parse-date-in-mysql) – Artem Ilchenko May 30 '19 at 09:30
  • @Jonnix date("Y-m-d h:i:s A") in this format i am inserting to the column –  May 30 '19 at 09:32
  • Right? But you don't have to. You could have inserted in the expected datetime format, into a datetime field. BUT, you have an alternative answer, so it's just for information :) – Jonnix May 30 '19 at 09:33

2 Answers2

0

You can fetch date like a normal string from the database then you need to use strtotime which parses an English textual DateTime into a Unix timestamp. Then you can use date function which returns the formatted date string.
I have passed a static string. You can pass your string variable which you are fetching from the database $time = strtotime($date_string_from_database);

<?php
$time = strtotime('4/14/2018 12:00:00 AM');

$newformat = date('m/d/Y',$time);

echo $newformat;
?>

You can see the live demo here

Zain Farooq
  • 2,956
  • 3
  • 20
  • 42
0

You can run this query to get your output,

SELECT DATE_FORMAT(STR_TO_DATE(created_at, "%Y-%m-%d"), "%Y-%m-%d") FROM  
self_balance

First I am matching date format and converting it to date and then formatting.

Rahul
  • 18,271
  • 7
  • 41
  • 60