Date of Birth

[bxgirl]

I'm trying to order the date of birth in descending order regardless of the year. Any tricks. I've trying creating a column of datatype int and store the concatenation of Month and day but that didn't work.

Thanks.

 

[bxgirl]

I figured it out. However, I'm curious to see other elegant solutions.

 

[guelphdad]

you wouldn't store dates in an INT column unless you are storing UNIX TIMESTAMPS there. But since you are working with birthdates you wouldn't use those as you can't create a UNIX TIMESTAMP prior to the epoch time in 1970.

Use a DATE type for your columns.

Store your dates as strings:
'1964-01-14'
'1997-01-05'
etc.

then do this to order your dates:

select
yourdatecolumn
from yourtable
order by
month(yourdatecolumn) desc
day(yourdatecolumn) desc

that gives you the latest month first and within that month the latest date in the month.

If I misunderstand you then you can remove the desc from both of those columns in the order by clause.

 

[bxgirl]

It turns out that they are interested in the year. So birthdays should appear in descending order by year, month and day

Ex.

2006-06-15
2005-06-14
2003-06-14
2003-06-12
2006-06-01
2004-06-01

Will your solution work for this as well?

 

[lgarner]

Your list is in descending order by month & day. guelphdad's method will display exactly that.

If you really mean "descending order by year, month and day", then simply use "order by yourdatefield desc". This will move the last two rows of your example further up.

 

[guelphdad]

It appears that you want to show it by month descending,
within each month you want to show the latest date in the month and descend to the earliest, and if the month and day are in the same month show the latest year then earliest year. at least that is what your data suggests. If so do this:

select
yourdatecolumn
from yourtable
order by
month(yourdatecolumn) desc
day(yourdatecolumn) desc
year(yourdatecolumn) desc

You can't merely order by date since that would give exact descending order to the entire dates and that doesn't look like what you are trying to do.

 

[bxgirl]

Thanks to everyone. I will try this.