3
\$\begingroup\$

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

\$\endgroup\$
1
  • \$\begingroup\$ I am not sure that you can make this "cleaner" or more readable. it looks pretty straight forward. \$\endgroup\$
    – Malachi
    Nov 6, 2013 at 16:02

2 Answers 2

3
\$\begingroup\$
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;
\$\endgroup\$
7
  • \$\begingroup\$ what are you doing? can you elaborate on this answer? I am a little lost myself. \$\endgroup\$
    – Malachi
    Nov 27, 2013 at 0:05
  • \$\begingroup\$ @Malachi Sorry, the previous revision didn't actually work with SQL Server. I've edited it into a proper answer now. \$\endgroup\$ Nov 27, 2013 at 5:42
  • \$\begingroup\$ 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 \$\endgroup\$
    – Malachi
    Nov 27, 2013 at 6:23
  • 1
    \$\begingroup\$ @Malachi The Coming CTE might be a bit excessive; you can easily undo that part if you prefer. \$\endgroup\$ Nov 27, 2013 at 7:13
  • 1
    \$\begingroup\$ @Malachi It's not that hard to remove the Coming CTE. \$\endgroup\$ Nov 27, 2013 at 17:25
2
\$\begingroup\$

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

\$\endgroup\$
6
  • \$\begingroup\$ OUCH!! MY EYES BLEED!! (that's just me I guess) \$\endgroup\$ Nov 19, 2013 at 18:34
  • \$\begingroup\$ @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... \$\endgroup\$
    – Malachi
    Nov 19, 2013 at 18:40
  • 1
    \$\begingroup\$ I meant all these caps aren't making it an easy read imho :) \$\endgroup\$ Nov 19, 2013 at 18:52
  • 4
    \$\begingroup\$ 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 \$\endgroup\$
    – Malachi
    Nov 19, 2013 at 18:53
  • 1
    \$\begingroup\$ @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. \$\endgroup\$
    – Malachi
    Nov 26, 2013 at 16:58

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.