Automate date to always show current Tuesday

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  September 6, 2018 at 4:14 am

  #362723

  Hi Guys,

  At the moment I have to run the a query and manually change the date every Tuesday. So every Tuesday i update the below date to the current Tuesday's date. So this week it shows the below and then next Tuesday i will change the date to >='2018-09-11' .   Is there a way to automate this?

  Thanks

  WHERE C.ContDate >='2018-09-04'

• Rick-153145

  SSCrazy

  Points: 2823

  More actions

  September 6, 2018 at 4:23 am

  #2004666

  
SELECT
      CASE WHEN DATEPART(WEEKDAY, GETDATE()) >2 THEN DATEADD(DAY, +1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
      ELSE DATEADD(DAY, -6, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
      END
  

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 6, 2018 at 7:47 am

  #2004706

  Just to demonstrate the validity of the code provided by Rick-153145. try running this:
  SET NOCOUNT ON;
DECLARE @Today AS date = '20180902';

  WITH Numbers AS (

      SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
    Tally AS (

          SELECT TOP (28) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS N
        FROM Numbers AS N1,
            Numbers AS N2
),
    TodayValues AS (

          SELECT DATEADD(day, T.N, @Today) AS Today
        FROM Tally AS T
)
SELECT
    TV.Today,
    DATENAME(weekday, TV.Today) AS Day_Name,
    CONVERT(date,
        CASE
            WHEN DATEPART(WEEKDAY, TV.Today) > 2 THEN DATEADD(day, 1, DATEADD(week, DATEDIFF(week, 0, TV.Today), 0))
            ELSE DATEADD(day, -6, DATEADD(WEEK, DATEDIFF(WEEK, 0, TV.Today), 0))
        END) AS Tuesday,
    DATENAME(weekday,
        CONVERT(date,
            CASE
                WHEN DATEPART(WEEKDAY, TV.Today) > 2 THEN DATEADD(day, 1, DATEADD(week, DATEDIFF(week, 0, TV.Today), 0))
                ELSE DATEADD(day, -6, DATEADD(WEEK, DATEDIFF(WEEK, 0, TV.Today), 0))
            END)
        ) AS Tuesday_Day_Name
FROM TodayValues AS TV
ORDER BY TV.Today;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  September 6, 2018 at 8:02 am

  #2004710

  Zero date is Monday.
  To get Tuesday we need to add 1 day to zero date:
  select datename(dw, dateadd(dd, 1, 0))
  To get this week Tuesday we need to add to that "zero Tuesday" the number of days within whole weeks between "zero Tuesday" and the current date.
  Because "zero Tuesday" is Day 1:

  select [Last Tuesday] =  dateadd(dd, datediff(dd, 1, getdate())/7*7, 1)
  

  _____________
  Code for TallyGenerator

• Jeff Moden

  SSC Guru

  Points: 1004313

  More actions

  September 6, 2018 at 8:48 am

  #2004719

  Sergiy - Thursday, September 6, 2018 8:02 AM

  Zero date is Monday.
  To get Tuesday we need to add 1 day to zero date:
  select datename(dw, dateadd(dd, 1, 0))
  To get this week Tuesday we need to add to that "zero Tuesday" the number of days within whole weeks between "zero Tuesday" and the current date.
  Because "zero Tuesday" is Day 1:

  select [Last Tuesday] =  dateadd(dd, datediff(dd, 1, getdate())/7*7, 1)
  

  There we go!  Beat me to it!

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Conficker

  SSCommitted

  Points: 1567

  More actions

  September 7, 2018 at 2:31 am

  #2004794

   
SELECT DATEADD(dd,0,DATEDIFF(dd,0,(GETDATE() - DATEPART(dw,GETDATE()) )+3))
  

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  September 7, 2018 at 5:17 am

  #2004818

  Conficker - Friday, September 7, 2018 2:31 AM

   
SELECT DATEADD(dd,0,DATEDIFF(dd,0,(GETDATE() - DATEPART(dw,GETDATE()) )+3))
  

  This solution, and the one posted by Rick are not good.
  They rely on specific settings of DATEFIRST.
  Here is the proof:
  set datefirst 1

  SELECT @@DATEFIRST [datefirst], DATEADD(dd,0,DATEDIFF(dd,0,(GETDATE() - DATEPART(dw,GETDATE()) )+3)) [Expected Tuesday]

  set datefirst 7

  SELECT @@DATEFIRST [datefirst], DATEADD(dd,0,DATEDIFF(dd,0,(GETDATE() - DATEPART(dw,GETDATE()) )+3)) [Expected Tuesday]

  To make them right you need to add @@DATEFIRST value into the formula:
  set datefirst 1

  SELECT @@DATEFIRST [datefirst], DATEADD(dd,0,DATEDIFF(dd,0,(GETDATE() - DATEPART(dw,GETDATE()) ) + 4 - (@@DATEFIRST+1)%7)) [Expected Tuesday]

  set datefirst 2

  SELECT @@DATEFIRST [datefirst], DATEADD(dd,0,DATEDIFF(dd,0,(GETDATE() - DATEPART(dw,GETDATE()) )+ 4 - (@@DATEFIRST+1)%7)) [Expected Tuesday]
set datefirst 6

  SELECT @@DATEFIRST [datefirst], DATEADD(dd,0,DATEDIFF(dd,0,(GETDATE() - DATEPART(dw,GETDATE()) )+ 4 - (@@DATEFIRST+1)%7)) [Expected Tuesday]

  set datefirst 7

  SELECT @@DATEFIRST [datefirst], DATEADD(dd,0,DATEDIFF(dd,0,(GETDATE() - DATEPART(dw,GETDATE()) )+ 4 - (@@DATEFIRST+1)%7)) [Expected Tuesday]

  _____________
  Code for TallyGenerator

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  September 7, 2018 at 5:26 am

  #2004824

  And still cannot beat 
  datename(dw, 1) = 'Tuesday'
  in terms of simplicity, reliability and performance.
  🙂

  _____________
  Code for TallyGenerator

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  September 7, 2018 at 5:32 am

  #2004826

  Thank you all much appreciated