4

I understand SQL Server 2005 doesn't support DATE and so casting a datetime as a date is not possible.

What I want to do is list all records with a datetime within an entire day of the current week.

So I need all records created on Monday of current week ignoring the timestamp. Currently I am using the below, which does show Monday-Friday of current week, but includes the timestamp, which means I'll see different output depending on when the query is run:

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

Can anyone assist?

My expected output would be a column for each day of the current week, where the week starts on Monday (i.e.: Datefirst 1). Ultimately I'm going to use it in a case statement like:

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

...Repeating for each day of the week. I need to accommodate for IntegerColumn values which are added throughout Monday. If no DateColumn entries occur within the hours of Monday, a zero is displayed.

  • 1
    you want 1 row with a column for each day. ie today would be in column Thursday with value 2016-02-11 00:00:00.000 ? Perhaps expected output would help – Julien Vavasseur Feb 11 '16 at 10:46
  • I have added a complete sample to the answer and it should cover what you need. – Julien Vavasseur Feb 11 '16 at 15:58
7

Today's date is 2016-02-11 and this query returns 2016-02-11 00:00:00.000:

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

It can then be included into the DATEADD statement:

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

This will give this output:

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

This query returns Sunday's date (2016-02-06 this week):

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

It can be used with this query and it gives the same output:

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

For records with a date on Monday, this query can be used:

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) 

It outputs rows where the date is bigger or equal to Monday at 00:00 and before Tuesday at 00:00.

It is better to use date >= '20160101 00:00' and date < '20160102 00:00' than BETWEEN. See: Why does my query search datetime not match?

Full sample (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)

Query 1:

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

Query 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
  • Msg 207, Level 16, State 1, Line 4 Invalid column name 'curDay'. for the first query – Peter Feb 11 '16 at 12:39
  • 1
    answer has been updated/fixed – Julien Vavasseur Feb 11 '16 at 13:06
  • If I wanted all the records created Monday, can't I use: select * from TABLENAME where DATECOLUMN Between DATEADD(day, 2 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) and DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) ? Doesnt seem to work – Peter Feb 11 '16 at 15:30
  • 2
    see update. it is usualy a bad idea to use between for date. date >= 20150101 00:00 and date < 20150102 00:00 is better. – Julien Vavasseur Feb 11 '16 at 15:36
  • Magnificent, thank you very much for your thorough answer Julien. This is exactly what I needed. I had never dealt with SQL 2005 in this way before so your answer has been extremely helpful. – Peter Feb 11 '16 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.