开发者

How to use CASE together with date INTERVAL?

开发者 https://www.devze.com 2023-01-24 12:42 出处:网络
I want to have this query: SELECT DATE(DATE_SUB(DATE(\'2010-09-10\'), (CASE DATETYPE WHEN \'H\' THEN INTERVAL 1 WEEK ELSE INTERVAL 1 YEAR END CASE))) AS wdt

I want to have this query:

SELECT DATE(DATE_SUB(DATE('2010-09-10'), (CASE DATETYPE WHEN 'H' THEN INTERVAL 1 WEEK ELSE INTERVAL 1 YEAR END CASE))) AS wdt 

MySQL says it's invalid

also I have tried

 SELECT _DATE AS wdt 
 UNION ALL 
 CASE DATETYPE
        WHEN 'H' THEN SELECT DATE(DATE_开发者_开发百科SUB(_DATE, INTERVAL 1 YEAR)) AS wdt ;
        ELSE SELECT DATE(DATE_SUB(DATE('2010-09-10'), INTERVAL 1 WEEK )) AS wdt;
  END CASE;
  UNION ALL 
  SELECT DATE(DATE_SUB(_DATE, INTERVAL 2 WEEK)) AS wdt 

which doesn't work also, error in query syntax.

How can I select interval 1 year for dates that are Holidays.


Using two DATE_SUB operations would work:

SELECT IF (DATETYPE  = 'H', 
           DATE_SUB(DATE('2010-09-10'), INTERVAL 1 WEEK),
           DATE_SUB(DATE('2010-09-10'), INTERVAL 1 YEAR)) 
       as wdt;
0

精彩评论

暂无评论...
验证码 换一张
取 消