比较 T-SQL 中的日期

发布于 2025-01-13 00:15:17 字数 510 浏览 2 评论 0原文

我有一个表，其中有一列数据类型为 varchar 的 date。值为'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'。请注意月份和日期数字中的前导零。

原文

I have a table with a column date of datatype varchar. The values are '2022-03-08 07:00',2022-03-08 07:30 ....

In my stored procedure I have a parameter of type DATE and with a value '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

How can I compare these two? I get this error:

Conversion failed when converting date and/or time from character string.

I would like to remove time component and compare '2022-03-08' vs '2022-3-8'. Notice leading zero in month and day numbers.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笑，眼淚并存 2025-01-20 00:15:17

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

TRY_CAST or TRY_CONVERT will convert your string into date and return null if that is not possible.

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

Example:

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

回复收藏 0 原文

束缚ｍ 2025-01-20 00:15:17

使用 right(replicate('0',2)+value,2) 可以将1 位数字更改为两位数字（ 1=>01）。
使用 PARSENAME 表示分割，使用 concat 表示连接字符串

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*/)

use right(replicate('0',2)+value,2) that enables you to change a 1 one_digit number to two_digit number(1=>01).
use PARSENAME for split and concat for connect strings

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

or in your query

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*/)

回复收藏 0 原文

~没有更多了~