Convert date and time value to string

Question

I get the current date and time using NOW () in MySQL. I want to convert the date value to varchar and combine it with another string. How should I do it?

Answer 1

Use DATE_FORMAT ()

SELECT DATE_FORMAT(NOW(), '%d %m %Y') AS your_date; 

Answer 2

This is super old, but I decided that I would add 2c. DATE_FORMAT really returns a string, but I was looking for the CAST function in a situation where I already had a datetime string in the database and needed to map a template to it:

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

In this case you should use:

CAST(date_value AS char)

This answers a slightly different question, but the title of the question seems rather ambiguous that it might help someone find it.

Answer 3

Try the following:

 concat(left(datefield,10),left(timefield,8)) 

• 10 char in the date field based on the full date yyyy-MM-dd .

• 8 char in the time field based on the total time hh:mm:ss .

It depends on the format you want. usually you can use the script above and you can concatenate another field or string as you want.

Because the actual date and time field flows like a string if you read it. But, of course, you will get an error while updating or paste it.