Convert datetime value into string

Question

I am fetching the current date & time using NOW() in mysql. I want to convert the date value into a varchar and concat it with another string. How do I do it?

score 158 · Accepted Answer · answered Mar 06 '10 at 12:24

158

Use DATE_FORMAT()

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

answered Mar 06 '10 at 12:24

Frank Heikens

117,544
24
142
135

score 74 · Answer 2 · answered Aug 06 '13 at 14:18

74

This is super old, but I figured I'd add my 2c. DATE_FORMAT does indeed return a string, but I was looking for the CAST function, in the situation that I already had a datetime string in the database and needed to pattern match against it:

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

In this case, you'd use:

CAST(date_value AS char)

This answers a slightly different question, but the question title seems ambiguous enough that this might help someone searching.

answered Aug 06 '13 at 14:18

Chords

6,720
2
39
61

The title is very clear about the question. Khilen wanted to just convert the date field into string. Your answer covers the comparison of two date strings which is not the relevant answer. Down-voted the answer. – Kashif Umair Liaqat Sep 04 '13 at 07:18
11

Actually, @KULKING, I compared the two dates as strings, which makes this specifically applicable to to the question being asked. The issue here is type conversion, which my answer addresses, albeit with a small amount of my own issue's background for context. – Chords Sep 04 '13 at 11:58
3

+1 - nice quick way to get a string (p.s. There's some pettiness on here sometimes ;) ) – Matthew Lock Jul 05 '14 at 09:14
You don't need to use `CAST`. If you use any string operators with a date, it will convert it to a string automatically. – Barmar Nov 24 '15 at 17:57
1

This helps in situations where you need to compare the date to an actual string. DATE_FORMAT fails in these cases with a `Illegal mix of collations` fatal error – MacroMan Apr 20 '18 at 14:49

score 5 · Answer 3 · edited Sep 01 '16 at 09:10

Try this:

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

10 char on date field based on full date yyyy-MM-dd.
8 char on time field based on full time hh:mm:ss.

It depends on the format you want it. normally you can use script above and you can concat another field or string as you want it.

Because actually date and time field tread as string if you read it. But of course you will got error while update or insert it.

Convert datetime value into string

3 Answers3

Linked