SQL日期计算 [英] SQL Date Calculations

问题描述

我有一个带有Last_Modified字段的表作为VARCHAR（55），它以2/15/08 1:34 PM的格式存储日期。我需要做的是查询数据库并选择从当前日期/时间起具有Last_Modified字段> = 6天的所有行。看来我需要转换日期，以便我可以使用mysql计算，但我不确定从哪里开始。

I have a table with with Last_Modified field as VARCHAR(55) which stores dates in the format of 2/15/08 1:34 PM. What I need to do is query the database and select ALL rows that have the Last_Modified field >= 6 days from the current date/time. It appears I will need to convert the dates so I can use the mysql calculations but I''m unsure where to begin on this.

推荐答案

我有一个带有Last_Modified字段的表作为VARCHAR（55），它以2/15/08 1:34 PM的格式存储日期。我需要做的是查询数据库并选择从当前日期/时间起具有Last_Modified字段> = 6天的所有行。看来我需要转换日期，以便我可以使用mysql计算，但我不确定从哪里开始。

I have a table with with Last_Modified field as VARCHAR(55) which stores dates in the format of 2/15/08 1:34 PM. What I need to do is query the database and select ALL rows that have the Last_Modified field >= 6 days from the current date/time. It appears I will need to convert the dates so I can use the mysql calculations but I''m unsure where to begin on this.

您首先必须将日期从VARCHAR字段修改为正常的MySQL日期格式，如

You first have to rework your date from the VARCHAR field into a ''normal'' MySQL date format like

展开 | 选择 | Wrap | 行号

您首先必须将日期从VARCHAR字段修改为正常的MySQL日期格式，如

You first have to rework your date from the VARCHAR field into a ''normal'' MySQL date format like

展开 | < span class =codeLinkonclick =selectAll（this）;>选择 | Wrap | 行号

是STR_TO_DATE也可以处理时间部分。如果格式字符串包含日期和时间部分，则STR_TO_DATE（）返回DATETIME值;如果字符串仅包含日期或时间部分，则返回DATE或TIME值。


更好地查看关于 STR_TO_DATE 或同一手册中的日期和时间函数章节这里


Ronald

Yes STR_TO_DATE also can handle the time part. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.

Better look at the MySQL documentation on STR_TO_DATE or the Date and Time function chapter in same manual HERE

Ronald

这篇关于SQL日期计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！