Home > Blockchain >  SQL WHEN statements
SQL WHEN statements

Time:07-08

I want to return the values in quotes if the DOB is within the dates shown, but the data comes back just showing the ELSE value only. Any ideas?

CASE
WHEN [Date of Birth] between 01/01/1945 and 31/12/1964 then 'Baby Boomer'
WHEN [Date of Birth] between 01/01/1965 and 31/12/1981 then 'Gen X'
WHEN [Date of Birth] between 01/01/1982 and 31/12/1994 then 'Millenial / Gen Y'
WHEN [Date of Birth] between 01/01/1995 and 31/12/2010 then 'Gen Z'
WHEN [Date of Birth] =>01/01/2011 then 'Gen Alpha'
ELSE 'Other TBC'
END

CodePudding user response:

The dates should be in quotes, right? If this is TSQL:

Also, you should change '31/12/1964' to '12/31/1964' it should be MONTH then DAY then YEAR.

This works:

CASE
WHEN [Date of Birth] between '01/01/1945' and '12/31/1964' then 'Baby Boomer'
WHEN [Date of Birth] between '01/01/1965' and '12/31/1981' then 'Gen X'
WHEN [Date of Birth] between '01/01/1982' and '12/31/1994' then 'Millenial / Gen Y'
WHEN [Date of Birth] between '01/01/1995' and '12/31/2010' then 'Gen Z'
WHEN [Date of Birth] >= '01/01/2011' then 'Gen Alpha' -- changed => to >=
ELSE 'Other TBC'
END
  •  Tags:  
  • sql
  • Related