1

How do I use a case statement to determine whether the current date is a saturday, sunday or a weekday? I have tried the following so any pointers on where I'm going wrong would be appreciated. Thanks.

SELECT * FROM mytable WHERE day = (SELECT CASE aday
                                        WHEN DATENAME(dw, GETDATE()) = 'Saturday' THEN 'Sat'
                                        WHEN DATENAME(dw, GETDATE()) = 'Sunday' The 'Sun'
                                        ELSE 'Weekday');
cn007b
  • 16,596
  • 7
  • 59
  • 74
user3075259
  • 315
  • 2
  • 15
  • what are your table field definitions for `day` and `aday` ? – hexerei software Apr 03 '15 at 21:21
  • `case aday` should just be `case`. And you are missing the `end`. Also no reason to write this as a subquery though that is valid. – Martin Smith Apr 03 '15 at 21:23
  • `SELECT * FROM mytable WHERE day = CASE WHEN DATENAME(dw, GETDATE()) = 'Saturday' THEN 'Sat' WHEN DATENAME(dw, GETDATE()) = 'Sunday' Then 'Sun' ELSE 'Weekday' end;` – Martin Smith Apr 03 '15 at 21:25
  • Thanks Martin, thought that had sorted it but I'm in Oracle not SQL SERVER and no joy, have put it up again as a new question. – user3075259 Apr 03 '15 at 23:20

1 Answers1

1
SELECT * FROM mytable WHERE day = (
    SELECT CASE DAYNAME(aday)
        WHEN 'Saturday' THEN 'Sat'
        WHEN 'Sunday' THEN 'Sun'
        ELSE 'Weekday' END
)
;
cn007b
  • 16,596
  • 7
  • 59
  • 74