在Mysql中将DateTime值转换为字符串

I want to convert Date & time value into varchar and then store in database

我想将日期和时间值转换为varchar，然后存储到数据库中

I am fetching the Current Date & time using NOW() in mysql and now want to convert it in string because i have to merge this value with String value

我在mysql中使用NOW()获取当前日期和时间，现在想要将它转换为字符串，因为我必须将这个值与字符串值合并

3 个解决方案

#1

101

Use DATE_FORMAT()

使用DATE_FORMAT()

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

#2

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:

这太老了，但我想我应该加上2c。DATE_FORMAT确实返回了一个字符串，但是我在寻找CAST函数，在我已经在数据库中有一个datetime字符串的情况下，需要对它进行模式匹配:

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

In this case, you'd use:

在这种情况下，您应该使用:

CAST(date_value AS char)

铸造(date_value字符)

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

这回答了一个稍微不同的问题，但问题标题似乎很模糊，这可能有助于搜索。

#3

Try this:

试试这个:

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

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

10基于完整日期yyyyy - mm -dd的日期字段char。
8 char on time field based on full time hh:mm:ss.

8基于全时间hh:mm:ss的时间场char

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.

因为实际上日期和时间字段都是字符串。但是，当更新或插入时，当然会出现错误。

#1

101