Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008
    Posts
    55

    Unanswered: Case statement in SET @ ??

    Hello Everyone,

    I am trying to create a Set @ based on today's day of week value. Here is the logic in plain English:

    If the today is Monday then value = 3 days back otherwise value = 1 day back.

    Here is my attempt:


    declare @DataDate datetime;
    set @DataDate = (case when datename(getdate()) = ''Monday'' then (DATEADD(dd,0,DateDiff())-3)) else DATEADD(dd,0,DateDiff())-1);

    When I run this code I get this error:
    Msg 174, Level 15, State 1, Line 4
    The datename function requires 2 argument(s).


    How do I make this work? Any input would be greatly appreciated.
    Thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Enjoy!

    Code:
    SELECT d, DateAdd(d, -CASE DateName(dw, d) WHEN 'Monday' THEN 3 ELSE 1 END, d)
       FROM (SELECT DateAdd(d, number, '2015-01-01') AS d
          FROM master.dbo.spt_values AS d
    	  WHERE  'P' = d.type) AS d
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •