order date for values with unknown day and month

Question

I'm developing a website with events from the past. For some events the whole date is known, for some the month of the year and for some only the year. Table event looks like:

id
content
date -> YYYY-MM-DD (date data type)
year -> true, if only year is known, otherwise false
monthandyear -> true if only year and month is known, otherwise false

I would like to list the contents of the table in the following order:

• descending by date
• if only the month and year are known (monthandyear is set to true), the content should be listed as the last of that month
• if only the year is known (year is set to true), the content should be listed as the last of that year

What is the best solution to this problem?

I'm using DQL:

SELECT p FROM App:Event p ORDER BY p.date DESC ...

Answer 1

You can use conditional logic. Assuming that date is a string:

order by left(date, 4),
         (case when not year and not yearmonth then 1 else 2 end),
         left(date, 7),
         (case when not yearmonth then 1  else 2 end)
         date

Edit:

If date is of type date, then:

order by year(date),
         (case when not year and not yearmonth then 1 else 2 end),
         month(date),
         (case when not yearmonth then 1  else 2 end)
         date