Concatenating date functions mysql

Question

A mysql query is returning unexpected. I'm trying to concatenate a few strings containing date queries like so:

mysql> select month(timestamp) +'-'+ day(timestamp) + '-' + 
       year(timestamp) as date FROM table ORDER BY timestamp DESC LIMIT 1;

and I'm getting a 4 digit result, which I assume is the sum of the expected result:

+------+
| date |
+------+
| 2035 |
+------+

I've also tried casting each one as CHAR which didn't work:

mysql> select CAST(month(timestamp) as CHAR) +'-'+ CAST(day(timestamp) as CHAR) + '-' + 
CAST(year(timestamp) as CHAR) as date FROM table ORDER BY timestamp DESC LIMIT 1;

Could someone let me know what I'm doing wrong? ... and I guess how to fix it would be nice too :)

+ is the addition operator (but works in MSSQL for string concatenation). — Salman Arshad
– Salman Arshad, Commented Jan 22, 2013 at 8:18

John Woo · Accepted Answer · 2013-01-22 08:13:01Z

5

In MySQL, use CONCAT_WS

CONCAT_WS('-', month(timestamp),day(timestamp),year(timestamp))

query,

SELECT CONCAT_WS('-', month(timestamp),day(timestamp),year(timestamp)) AS DATE
FROM   tablename
ORDER  BY timestamp DESC 
LIMIT  1

or use DATE_FORMAT

SELECT DATE_FORMAT(timestamp, '%m-%d-%Y') AS DATE
FROM   Table1
ORDER  BY timestamp DESC 
LIMIT  1

SQLFiddle Demo

Other(s):

edited Jan 22, 2013 at 8:13

answered Jan 22, 2013 at 8:06

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

d-_-b Over a year ago

awesome! plain old CONCAT worked perfectly. forgot about those ... thank you!

Salman Arshad · Accepted Answer · 2013-01-22 08:13:03Z

2

Using CONCAT, DAY, MONTH, YEAR, etc to format dates is silly.

If timestamp is an integer column containing a UNIX timestamp use this:

SELECT DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%m-%d-%Y') AS `Formatted Date`
...

If timestamp is a date/datetime column use this:

SELECT DATE_FORMAT(`timestamp`, '%m-%d-%Y') AS `Formatted Date`
...

answered Jan 22, 2013 at 8:13

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Collectives™ on Stack Overflow

Concatenating date functions mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related