MYSQL - SELECT statement that can name the results based on the condition

I would like to construct a SELECT statement that can name the results based on the condition. To better explain my attempt, I constructed the following invalid / non-working statement. How could the following SELECT statement be altered to one that actually works for my cause. Thx!

SELECT  
  CASE
     WHEN dayStart LIKE '2012-06-11%' THEN UNIX_TIMESTAMP(dayStart) as today
     WHEN dayStart LIKE '2012-06-10%' THEN UNIX_TIMESTAMP(dayStart) as yest
     WHEN dayStart LIKE '2012-06-12%' THEN UNIX_TIMESTAMP(dayStart) as tomor
  END
FROM dayBook
WHERE userID = 1

Answers


Why not just return two columns, one that tells you the date, the other that tells you if it's today, yesterday, or tomorrow?

SELECT  
  UNIX_TIMESTAMP(dayStart) as yourDate
, CASE
     WHEN dayStart LIKE '2012-06-11%' THEN 'today'
     WHEN dayStart LIKE '2012-06-10%' THEN 'yest'
     WHEN dayStart LIKE '2012-06-12%' THEN 'tomor'
  END as dayType
FROM dayBook
WHERE userID = 1

You'll need to use Dynamic SQL.


I'm confused on your question. If you are wanting to change a value of UNIX_TIMESTAMP You could do an UPDATE dayBook SET UNIX_TIMESTAMP(dayStart) = 'Today' WHERE dayStart = '2012-06-11%' and do the same for the other ones.


Need Your Help

java polymorphism

java polymorphism

Consider the following code:

German unicode letters don't show up in app

android unicode encoding letters

I have a problem with language encoding... what i try is to incorporate a random motivational string into my app which contains German unicode letters... as far as I know, Java uses Unicode-16, but...