The day() function gives the numeric day for a month irrespective of which month it's in which is why you're having a problem. As long as your dates are dates and not datetimes something like below should work
IF [load_date] =DATEADD('day', -1, today()) THEN 'today'
ELSEIF [load_date]=DATEADD('day', -2, TODAY() ) THEN 'yesterday'
ELSEIF [load_date]=DATEADD('day', -8, TODAY() ) THEN 'a week ago'
If they're datetimes then you'll need to change [load_date] to datetrunc('day',[load_date])
Let me know if that helps
Thanks, figured that this was because load_date was in datetime format.
Datetrunc funtion fixed this issue.
Thanks for the help.