查询varchar日期与之间 [英] Query varchar date with between

问题描述

如何使用 BETWEEN 运算符选择存储为 varchar(格式:2012.04")的日期?

How can i select a date stored as varchar (format: "2012.04") with the BETWEEN operator?

我需要在此字段中选择一年间隔.

I need to select a one year interval on this field.

现在我正在尝试这个，但这给了我的 MySQL 语法错误:

Now i'm trying with this, but this gives my MySQL syntax error:

SELECT DISTINCT monthcol
FROM bo_alerts
WHERE STR_TO_DATE(monthcol , '%Y.%m') BETWEEN
           (STR_TO_DATE('2012.04', '%Y.%m') AND STR_TO_DATE('2011.03', '%Y.%m'))
ORDER BY monthcol DESC

日期现在是静态的(用于测试)，但我需要计算 BETWEEN 中的第二个值，以便它与 BETWEEN 部分的第一个值精确 -1 年.

The dates are now static (for testing), but i need to calculate the second value in the BETWEEN so that it will be exact -1 year to the first value of the BETWEEN section.

非常感谢！！

推荐答案

鉴于日期似乎是定长字符串，为什么不使用字符串比较?:

Given that the dates seem to be fixed length strings, why not use string comparison?:

SELECT DISTINCT monthcol
FROM bo_alerts
WHERE monthcol BETWEEN '2011.03' AND '2012.04'
ORDER BY monthcol DESC

请注意，我颠倒了日期的顺序以满足大多数 SQL 实现的第一个小于第二个.

Notice that I reversed the order of the dates to satisfy most SQL implementations having the first less than the second.

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