CODEWITHSUNDEEP
mysqlsql
Marc Rasmussen
Marc Rasmussen

Reputation: 20555

Select date from timestamp SQL

i have the following row in my database:

'1', '1', '1', 'Hello world', 'Hello', '2014-01-14 17:33:34'

Now i wish to select this row and get only the date from this timestamp:

i have tried the following:

SELECT title,   FROM_UNIXTIME(timestamp,'%Y %D %M') AS MYDATE FROM Team_Note TN WHERE id = 1

I have also tried:

SELECT title,   DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%e %b %Y') AS MYDATE FROM Team_Note TN WHERE id = 1

However these just return an empty result (or well i get the Hello part but MYDATE is empty)

Upvotes: 6

Views: 106218

Answers (3)

Rogério Viana
Rogério Viana

Reputation: 170

In PostgreSQL:

SELECT DATE(r.data_registro) FROM registro_financeiro r WHERE id = 1;

Upvotes: 5

Adarsh Shah
Adarsh Shah

Reputation: 6775

You need to use DATE function for that:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date

SELECT title,   
DATE(timestamp) AS MYDATE 
FROM Team_Note TN 
WHERE id = 1

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Did you try this?

SELECT title, date(timestamp) AS MYDATE
FROM Team_Note TN
WHERE id = 1

Upvotes: 27

Related Questions