Sorting a date hired field for anniversaries

[Mark1inLA]

Hi all,

 

I've been trying to figure this out for quite sometime and haven't been able to resolve it yet. Here is some sample data:

 

id

name

hire_date

1

john

2005-01-10

2

jane

2009-12-10

3

bill

2007-11-10

 

How can i get it to sort from the most recent anniversary date in descending order so that the order will show in this order: John, Jane, Bill

 

Thanks in advance!

 

[roopurt18]

SELECT ... FROM `yourtable` WHERE ... ORDER BY `hire_date` DESC

 

in this order: John, Jane, Bill

That's not descending order...

[Mark1inLA]

SELECT ... FROM `yourtable` WHERE ... ORDER BY `hire_date` DESC

 

in this order: John, Jane, Bill

That's not descending order...

 

I'm not exactly sure about descending or ascending orders for dates, so let me restate: from the most recent anniversary dates to the later.

[roopurt18]

When you say descending / ascending order, are you not concerned with the year portion?

 

I think what you will want to do is:

create table `myjunktable` (
name varchar( 22 ),
hire_date datetime
);

INSERT INTO myjunktable( name, hire_date ) 
VALUES (
'john',  '2005-01-10'
), (
'jane',  '2009-12-10'
), (
'bill',  '2007-11-10'
);

select 
*
from myjunktable
order by 
    -- We want to order by the number of days between today and the anniv_date
    -- regardless of sign, so we wrap it all in abs()
    abs( 
        -- The datediff calculates the number of days between the current time
        -- and that returned by the concat.
        -- If anniv_date is 2010-01-05 and today is 2010-01-13, datediff is: 8
        -- If anniv_date is 2010-01-14 and today is 2010-01-13, datediff is: -1
        datediff( 
            now(), 
                -- The concat takes the hire_date and calculates the anniversary 
                -- for this year.  i.e. hire_date,   anniv_date
                --                   2005-01-05         2010-01-05
                --                   2007-06-08         2010-06-08
                concat(             
                    date_format( now(), '%Y' ),          -- Returns current year as YYYY, i.e. 2010
                    date_format( hire_date, '-%m-%d' )   -- Returns month-day portion of hire_date, 
                                                         -- i.e. if hire_date is 2005-01-05, returns -01-05
                ) 
            ) 
        );

[Mark1inLA]

When you say descending / ascending order, are you not concerned with the year portion?

 

I think what you will want to do is:

create table `myjunktable` (
name varchar( 22 ),
hire_date datetime
);

INSERT INTO myjunktable( name, hire_date ) 
VALUES (
'john',  '2005-01-10'
), (
'jane',  '2009-12-10'
), (
'bill',  '2007-11-10'
);

select 
*
from myjunktable
order by 
    -- We want to order by the number of days between today and the anniv_date
    -- regardless of sign, so we wrap it all in abs()
    abs( 
        -- The datediff calculates the number of days between the current time
        -- and that returned by the concat.
        -- If anniv_date is 2010-01-05 and today is 2010-01-13, datediff is: 8
        -- If anniv_date is 2010-01-14 and today is 2010-01-13, datediff is: -1
        datediff( 
            now(), 
                -- The concat takes the hire_date and calculates the anniversary 
                -- for this year.  i.e. hire_date,   anniv_date
                --                   2005-01-05         2010-01-05
                --                   2007-06-08         2010-06-08
                concat(             
                    date_format( now(), '%Y' ),          -- Returns current year as YYYY, i.e. 2010
                    date_format( hire_date, '-%m-%d' )   -- Returns month-day portion of hire_date, 
                                                         -- i.e. if hire_date is 2005-01-05, returns -01-05
                ) 
            ) 
        );

 

 

Problem solved!

 

Thanks a bunch for your help and detailed comments.