Computing the week for a certain date

Question

I have the following SQL query that computes for every date the week it falls in (a week begins on Sunday and ends on Saturday):

SELECT EntryDate
 ,CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS DATE) AS 'SundayDate'
 ,CAST(DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS DATE) AS 'SaturdayDate'
 ,CONVERT(VARCHAR, DATEADD(DAY ,1-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) + ' - ' +
 CONVERT(VARCHAR, DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) AS 'Week'
FROM MyTable
WHERE CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS DATE) <=
 CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) AS DATE)
ORDER BY CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS DATE)

It works fine, but I don't like the repeated function calls because they cluster the query and make it less readable.

How can I make the query cleaner and more readable? (I'm using SQL Server 2008.)

• Malachi: I am not sure that you can make this "cleaner" or more readable. it looks pretty straight forward.

Answer 1

WITH Dates AS (
 SELECT EntryDate
 , DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS SundayDate
 , DATEADD(DAY, 7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS SaturdayDate
 FROM MyTable
), Coming AS (
 SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) AS Sunday
)
SELECT EntryDate
 , SundayDate
 , SaturdayDate
 , CONVERT(VARCHAR, SundayDate, 103) + ' - ' + CONVERT(VARCHAR, SaturdayDate, 103) AS Week
 FROM Dates, Coming
 WHERE SundayDate <= Coming.Sunday
 ORDER BY 2;

The simplifications I've made are:

• Extracted most of the query into a Common Table Expression named Dates to reduce redundancy.
• Removed the pointless CAST(... AS DATE), since DATEADD() already produces dates.
• Used a column number for ORDER BY. With the Common Table Expression, though, we could just as easily ORDER BY SundayDate.
• Extracted DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) into a Common Table Expression named Coming. This doesn't reduce the complexity of the query, but helps make the WHERE-clause read more like English.

SQLFiddle

---

Edit

Since the benefits of the last two suggestions are debatable, you may prefer a milder approach that incorporates just the first two suggestions:

WITH Dates AS (
 SELECT EntryDate
 , DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS SundayDate
 , DATEADD(DAY, 7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS SaturdayDate
 FROM MyTable
)
SELECT EntryDate
 , SundayDate
 , SaturdayDate
 , CONVERT(VARCHAR, SundayDate, 103) + ' - ' + CONVERT(VARCHAR, SaturdayDate, 103) AS Week
 FROM Dates
 WHERE SundayDate <= DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE())
 ORDER BY SundayDate;

• Malachi: what are you doing? can you elaborate on this answer? I am a little lost myself.
• 200_success: @Malachi Sorry, the previous revision didn't actually work with SQL Server. I've edited it into a proper answer now.
• Malachi: I don't like it, but it is functional. personally I like my query better than this one. your Query and mine have the exact same Execution plan. :) sqlfiddle.com/#!3/cf783/11
• 200_success: @Malachi The Coming CTE might be a bit excessive; you can easily undo that part if you prefer.
• 200_success: @Malachi It's not that hard to remove the Coming CTE.

Answer 2

You can lose the CAST that surrounds the DATEADD function.

The DATEADD function should spit out a DATETIME datatype.

It should look like this:

SELECT EntryDate
 ,DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS 'SundayDate'
 ,DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS 'SaturdayDate'
 ,CONVERT(VARCHAR, DATEADD(DAY ,1-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) + ' - ' +
 CONVERT(VARCHAR, DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) AS 'Week'
FROM MyTable
WHERE DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) <=
 DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) 
ORDER BY DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate)

In the ORDER BY statement you might be able to use the Alias from your SELECT Statement, which would really speed this up.

SELECT EntryDate
 ,DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) AS 'SundayDate'
 ,DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate) AS 'SaturdayDate'
 ,CONVERT(VARCHAR, DATEADD(DAY ,1-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) + ' - ' +
 CONVERT(VARCHAR, DATEADD(DAY ,7-DATEPART(WEEKDAY, EntryDate), EntryDate), 103) AS 'Week'
FROM MyTable
WHERE DATEADD(DAY, 1-DATEPART(WEEKDAY, EntryDate), EntryDate) <=
 DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) 
ORDER BY 'Sunday_Date`

I would put DESC on that order by because I would want the newest dates to show up first, if you are grabbing dates from the past

If you are grabbing from the future, then you would want 'ASC' in there, but that is default.

I Can't remember who said it but

it is as simple as it can be when there is nothing left that can be taken away

• Mathieu Guindon: OUCH!! MY EYES BLEED!! (that's just me I guess)
• Malachi: @retailcoder, unfortunately, when you use a function in the SELECT and give it an alias, you can't just use the alias in the WHERE clause, you have to calculate it again. the ORDER BY the OP Might be able to use the alias...
• Mathieu Guindon: I meant all these caps aren't making it an easy read imho :)
• Malachi: that is the coding syntax of SQL, most of the SQL engines are not case sensitive on these keywords though. but it makes it easier to pick them out when the code gets bigger, @retailcoder
• Malachi: @kodkod, if you need the date without the time part for reporting purposes, that should be done on the report side not on the database side. that should speed things up a little bit, because that is less functions running in the query. the reporting software would be able to do it easier I think.