How do i write a case statement to subtract 5 hours from datetime if it's before day light saving and subtract 4 hours if after

Question

I need to subtract 5 hours if the datetime falls before 03/09/2014 saving and 4 hours if it's after 03/09/2014. It's in a case statement this is what I have so far but it's only hitting the first when and not working with the second when.

max(
  Case 
       when aa.status_id=10 and aa.created_at <'3/09/2014'
            then DATE_SUB(aa.created_at,  interval 4 HOUR) 
       when aa.status_id=10 and aa.created_at >'3/09/2014'
            then DATE_SUB(aa.created_at,  interval 4 HOUR)
       else null
  end )as  form_Receieved

I have a typo when it's less 03/09/2014 is 5 hours and greater 03/09/2014 is 4 hours — user3115719, Apr 09 '14 at 19:24
You might not be seeing correct result because you are subtracting 4 in both cases. — rs., Apr 09 '14 at 19:26

score 0 · Answer 1 · answered Apr 09 '14 at 19:32

0

first you are substracting 4 hours from date ! which will not work , it must be datetime.

second you comparing between datetime column and date . you have to convert it to datetime. or make it same as your column format.

      when aa.status_id=10 and aa.created_at <'2014-03-09 00:00:00'

assuming that created_at is datetime column because you didnt show your table and columns structure.

answered Apr 09 '14 at 19:32

echo_Me

37,078
5
58
78

I changed it to your format and I'm subtracting but my created > 03/09/2014 00:00:00 – user3115719 Apr 09 '14 at 19:40
oops.. I wasn't done is an hour behind I'm getting 13 instead of 14. – user3115719 Apr 09 '14 at 19:42
if you post some sample data and wished result. would be much better. – echo_Me Apr 09 '14 at 19:57

How do i write a case statement to subtract 5 hours from datetime if it's before day light saving and subtract 4 hours if after

1 Answers1