代码之家 › 专栏 › 技术社区 › Gady

在WHERE子句中使用char类型的日期

where-clause char date sql-server sql

1

Gady · 技术社区 · 15 年前

我有一个SQL Server数据库表,其中有一个名为“日期”的char列(我知道,真的很糟糕,但我没有创建数据库),它的日期以这种格式存储为字符串:yymmdd。我需要在这些日期之间返回记录,所以将它们视为实际日期,我已经尝试了所有我知道的组合,但仍然会出错。任何帮助都非常感谢。我试过所有这些都没有运气:

SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 112) AS datetime) BETWEEN '100401' AND '101001'
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 12) AS datetime) BETWEEN '100401' AND '101001'
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 112) AS datetime) BETWEEN '100401' AND '101001'
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 12) AS datetime) BETWEEN '100401' AND '101001'
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 112) AS datetime) >= 100401 AND CAST(CONVERT(char(8), [DATE], 112) AS datetime) <= 101001
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 12) AS datetime) >= 100401 AND CAST(CONVERT(char(8), [DATE], 12) AS datetime) <= 101001
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 112) AS datetime) >= 100401 AND CAST(CONVERT(char(8), "DATE", 112) AS datetime) <= 101001
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 12) AS datetime) >= 100401 AND CAST(CONVERT(char(8), "DATE", 12) AS datetime) <= 101001
SELECT * FROM caddb..INCIDENT WHERE CAST([DATE] AS INT) >= 100401 AND CAST([DATE] AS INT) < 101001
SELECT * FROM caddb..INCIDENT WHERE CAST("DATE" AS INT) >= 100401 AND CAST("DATE" AS INT) < 101001
SELECT * FROM caddb..INCIDENT WHERE CAST([DATE] AS INT) BETWEEN 100401 AND 101001
SELECT * FROM caddb..INCIDENT WHERE CAST("DATE" AS INT) BETWEEN 100401 AND 101001

2 回复 | 直到 15 年前

1

0

bobs 15 年前

SELECT * 
FROM caddb..INCIDENT 
WHERE CONVERT(datetime, "DATE", 112) BETWEEN CONVERT(datetime, '100401', 112) and CONVERT(datetime, '101001', 112)

2

1

Endy Tjahjono 15 年前

convert(datetime, [DATE], 12) between '2010-10-01' and '2010-10-10'