Can I use MySQL date field type with custom date format

Question

Hi,

I have a MySQL database with two fields: Date and Value. My dates are quarters (Q1-2007, Q2-2008...). I want to be able to use the following SQL query:

SELECT * FROM table WHERE 1 ORDER BY Date

How can I do this?

Answer 1

I'm guessing you want them ordered so that all the 2007 ones appear in order followed by the 2008 ones, etc. This should work:

SELECT * FROM table WHERE 1 ORDER BY SUBSTRING(Date, 4, 4), SUBSTRING(Date, 1, 2);

Assuming your date formats are all consistent and that your 'Date' column is actually a VARCHAR or similar...

Answer 2

Use a date-column and decide on a date for each quarter. Then you can use the builtin functions for dates.

Answer 3

Doesn't SELECT * FROM table WHERE 1 ORDER BY Date already sort the field Date as Q1-2007, Q2-2007, Q3-2007, Q4-2007? If you sort such strings, that should be how they are sorted.

If then I would create a database to contain those values, I would rather use 3 fields, and use two different fields for quarter, and year.