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?
Add a comment
|
3 Answers
Use DATE_FORMAT()
SELECT
DATE_FORMAT(NOW(), '%d %m %Y') AS your_date;
Comments
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.
5 Comments
Kashif Umair Liaqat
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.
Chords
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.
Matthew Lock
+1 - nice quick way to get a string (p.s. There's some pettiness on here sometimes ;) )
Barmar
You don't need to use
CAST. If you use any string operators with a date, it will convert it to a string automatically.
MacroMan
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 errorTry 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.
