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.
Best Answer
Today's date is 2016-02-11 and this query returns
2016-02-11 00:00:00.000
:It can then be included into the
DATEADD
statement:This will give this output:
This query returns Sunday's date (
2016-02-06
this week):It can be used with this query and it gives the same output:
For records with a date on Monday, this query can be used:
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'
thanBETWEEN
. See: Why does my query search datetime not match?Full sample (SQL Fiddle):
Query 1:
Query 2: