比较T-SQL中的日期

Question

我有一个表，其列date的数据类型为varchar。值为'2022-03-08 07:00',2022-03-08 07:30 ...。

在我的存储过程中，我有一个类型为DATE、值为'2022-3-8'的参数

DECLARE @d DATE = '2022-3-8'
SELECT *, r.date AS date, @d AS d 
FROM Readings AS r
WHERE CONVERT(VARCHAR, r.date, 23) = @d

我该如何比较这两个？我得到这个错误：

从字符串转换日期和/或时间时转换失败。

我想删除时间分量并比较'2022-03-08'和'2022-3-8'。请注意，月份和日期的数字以零开头。

Answer 1

TRY_CAST或TRY_CONVERT会将字符串转换为日期，如果不可能，则返回null。

SELECT *, TRY_CAST(r.date AS DATE) as date, @d AS d
FROM Readings r
WHERE TRY_CAST(r.date AS DATE) = @d

示例：

DECLARE @d DATE = '2022-3-8';
WITH Readings AS 
(
SELECT '2022-03-08 07:00' AS date  
UNION ALL 
SELECT '2022-03-08 07:30'
UNION ALL
SELECT '2022-03-06 17:30' --will be false
UNION ALL
SELECT '2022-02-31 07:30' --invalid string
)
SELECT r.date as OriginalString
, TRY_CAST(r.date AS DATE) as CastDate
, TRY_CONVERT(DATE,r.date,23) as ConvertDate
, @d AS d
, CASE WHEN TRY_CAST(r.date AS DATE) = @d THEN 1 ELSE 0 END AS Matched
FROM Readings r

Answer 2

使用right(replicate('0'，2(+value，2(，使您能够将1一位数字更改为两位数字(1=>01(。拆分使用PARSENAME，连接字符串使用连接

DECLARE @d DATE = '2022-3-8'
SELECT Concat(( Parsename(Replace(@d, '-', '.'), 3) )/*year*/, '-', RIGHT(
Replicate('0', 2) + ( Parsename(Replace(@d, '-', '.'), 2) ), 2)
/*month*/,
'-', RIGHT(Replicate('0', 2) + ( Parsename(Replace(@d, '-', '.'),
1) ), 2
)/*day*/) as d

或在您的查询中

DECLARE @d DATE = '2022-3-8'
SELECT *,
r.date
AS date,
,@d
FROM   readings AS r
WHERE  CONVERT(VARCHAR, r.date, 23) = 
Concat(( Parsename(Replace(@d, '-', '.'), 3) )/*year*/, '-', RIGHT(
Replicate('0', 2) + ( Parsename(Replace(@d, '-', '.'), 2) ), 2)/*month*/,
'-',
RIGHT(Replicate('0', 2) + ( Parsename(Replace(@d, '-', '.'), 1) ), 2)
/*day*/)