主页 / dba / 问题 / 128967
Accepted
Peter
Asked: 2016-02-12 02:28:33 +0800 CST

日期时间仅作为日期

  • 772

我了解 SQL Server 2005 不支持DATE，因此无法将日期时间转换为日期。

我想要做的是列出当前一周一整天内具有日期时间的所有记录。

所以我需要在本周的星期一创建的所有记录忽略时间戳。目前我正在使用以下内容，它确实显示了本周的周一至周五，但包括时间戳，这意味着我将看到不同的输出，具体取决于查询的运行时间：

set datefirst 1

select 
    DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Monday,
    DATEADD(day, 2 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Tuesday,
    DATEADD(day, 3 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as wednesday,
    DATEADD(day, 4 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Thursday,
    DATEADD(day, 5 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Friday

有人可以帮忙吗？

我的预期输出将是当周每一天的一列，该周从星期一开始（即：Datefirst 1）。最终，我将在一个案例陈述中使用它，例如：

CASE
WHEN DateColumn >= DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) and DateColumn < DATEADD(day, 2 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE()))
THEN IntegerColumn
ELSE 0
END AS Monday

...重复一周中的每一天。我需要容纳整个星期一添加的 IntegerColumn 值。如果在星期一的几个小时内没有出现 DateColumn 条目，则显示零。

sql-server-2005 datetime

1 个回答

  1. Best Answer

    今天的日期是 2016-02-11，此查询返回2016-02-11 00:00:00.000：

    SELECT curDay = DATEADD(day, DATEDIFF(dd, 0, getdate()), 0)

    然后可以将其包含在DATEADD语句中：

    SELECT curDay = GETDATE()
    , DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Monday
    , DATEADD(day, 2 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Tuesday
    , DATEADD(day, 3 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as wednesday
    , DATEADD(day, 4 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Thursday
    , DATEADD(day, 5 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Friday

    这将给出以下输出：

    curDay                  | Monday                    | Tuesday                   | Wednesday                 | Thursday                  | Friday
2016-02-11 11:54:24.947 | 2016-02-08 00:00:00.000   | 2016-02-09 00:00:00.000   | 2016-02-10 00:00:00.000   | 2016-02-11 00:00:00.000   | 2016-02-12 00:00:00.000

    此查询返回星期日的日期（2016-02-06本周）：

    SELECT Sunday = DATEADD(day, DATEDIFF(dd, 0, getdate()) - DATEPART(weekday, GETDATE()), 0)

    它可以与此查询一起使用，并提供相同的输出：

    SELECT curDay
    , DATEADD(day, 1, Sunday) as Monday
    , DATEADD(day, 2, Sunday) as Tuesday
    , DATEADD(day, 3, Sunday) as wednesday
    , DATEADD(day, 4, Sunday) as Thursday
    , DATEADD(day, 5, Sunday) as Friday
FROM (SELECT Sunday = DATEADD(day, DATEDIFF(dd, 0, getdate()) - DATEPART(weekday, GETDATE()), 0)) as cd

    对于日期为星期一的记录，可以使用此查询：

    SELECT ...
FROM ...
WHERE colDate >= DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) 
    AND colDate < DATEADD(day, 2 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0)

    它输出日期大于或等于星期一 00:00 和星期二之前 00:00 的行。

    使用起来date >= '20160101 00:00' and date < '20160102 00:00'比BETWEEN. 请参阅：为什么我的查询搜索日期时间不匹配？

    完整示例（SQL Fiddle）：

    CREATE TABLE test(id int, [date] datetime)

INSERT INTO test values(1, '2016-02-07 23:23')
INSERT INTO test values(2, '2016-02-08 01:01')
INSERT INTO test values(3, '2016-02-08 12:00')
INSERT INTO test values(4, '2016-02-08 23:23')
INSERT INTO test values(5, '2016-02-09 00:00')

DECLARE @date datetime = '2016-02-11 12:34'
set datefirst 1

-- Query 1
SELECT id, [date]
    , IntegerColumn = CASE WHEN [date] >= DATEADD(day, 1 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0) 
            AND [date] < DATEADD(day, 2 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)
        THEN IntegerColumn
        ELSE 0
    END
FROM test

-- Query 2
SELECT id, [date], IntegerColumn
FROM test
WHERE [date] >= DATEADD(day, 1 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0) 
    AND [date] < DATEADD(day, 2 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)

    查询一：

    id  | date                      | IntegerColumn
1   | 2016-02-07 23:23:00.000   | 0
2   | 2016-02-08 01:01:00.000   | 2
3   | 2016-02-08 12:00:00.000   | 3
4   | 2016-02-08 23:23:00.000   | 4
5   | 2016-02-09 00:00:00.000   | 0

    查询 2：

    id  | date                      | IntegerColumn
2   | 2016-02-08 01:01:00.000   | 2
3   | 2016-02-08 12:00:00.000   | 3
4   | 2016-02-08 23:23:00.000   | 4
    • 7

相关问题