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.)

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;

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