Tags: date, minus, programming, return, select, sql, toda

Date minus one week day

On Programmer » SQL

2,522 words with 3 Comments; publish: Thu, 29 May 2008 04:38:00 GMT; (200312.50, « »)

I want to select data with a date that is one week day less than toda

e.g.

23/04/04 (Friday) would return 22/04/04 (Thursday

26/04/04 (Monday) would return 23/04/04 (Friday

24/04/04 (Saturday) would return 23/04/04 (Friday

Thanks

All Comments

Leave a comment...

  • 3 Comments
    • use dateadd function with -8 as the period to "add"

      "Kevin L" <anonymous.sql.questionfor.info.discussions.microsoft.com> wrote in message

      news:73231C4A-2524-4FE5-A3AE-299F9F34C1FE.sql.questionfor.info.microsoft.com...

      > I want to select data with a date that is one week day less than today

      > e.g.

      > 23/04/04 (Friday) would return 22/04/04 (Thursday)

      > 26/04/04 (Monday) would return 23/04/04 (Friday)

      > 24/04/04 (Saturday) would return 23/04/04 (Friday)

      > Thanks

      #1; Thu, 29 May 2008 04:39:00 GMT
    • A common approach to this sort of problem is to create a Calendar table.

      That way you can account for public holidays as well as weekends.

      CREATE TABLE Calendar

      (caldate DATETIME NOT NULL PRIMARY KEY,

      workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')

      Populate it with as many years data as you'll ever need:

      INSERT INTO Calendar (caldate) VALUES ('20000101')

      WHILE (SELECT MAX(caldate) FROM Calendar)<'20201231'

      INSERT INTO Calendar (caldate)

      SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),

      (SELECT MAX(caldate) FROM Calendar))

      FROM Calendar

      Set up the weekends:

      UPDATE Calendar SET workingday = 'N'

      WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')

      You can also identify relevant holidays yourself in the same way.

      Query to get previous working day:

      SELECT MAX(caldate)

      FROM Calendar

      WHERE caldate < CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)

      AND workingday = 'Y'

      --

      David Portas

      SQL Server MVP

      --

      #2; Thu, 29 May 2008 04:40:00 GMT
    • "Kevin L" <anonymous.sql.questionfor.info.discussions.microsoft.com> wrote in message

      news:73231C4A-2524-4FE5-A3AE-299F9F34C1FE.sql.questionfor.info.microsoft.com...

      > I want to select data with a date that is one week day less than today

      > e.g.

      > 23/04/04 (Friday) would return 22/04/04 (Thursday)

      > 26/04/04 (Monday) would return 23/04/04 (Friday)

      > 24/04/04 (Saturday) would return 23/04/04 (Friday)

      > Thanks

      DECLARE .sql.questionfor.info.date DATETIME

      SET .sql.questionfor.info.date = '20040425'

      SELECT CASE WHEN DATEPART(WEEKDAY, .sql.questionfor.info.date) < 3

      THEN .sql.questionfor.info.date - (DATEPART(WEEKDAY, .sql.questionfor.info.date) + 1)

      ELSE .sql.questionfor.info.date - 1

      END

      2004-04-23 00:00:00.000

      --

      JAG

      #3; Thu, 29 May 2008 04:41:00 GMT