Finding earliest date - ignoring year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding earliest date - ignoring year

Author

Topic

Speck
Starting Member

1 Post

Posted - 2012-09-15 : 00:14:34

Having a bit of trouble with this.

Need to find the record with the 'earliest date', with the year irrelevant.

So if my table has

03-07-80 (DD-MM-YY)
06-12-12
23-01-90

Then 23-01 should be the earliest date.

This works, but only finding the minimum date, not just the DD and MM part.


select min(date1), year1
from table
where date1 = ( select min(date1)
                   from table
                 )
group by year1, date1;

If I try changing it to


select to_char(min(date1),'DD-MM'), year1
from table
where date1 = ( select to_char(min(date1),'DD-MM')
                   from table               
                 )
group by year1, date1;

I get an invalid month error? And I'm not 100% sure on whether the to_char(min(date1),'DD-MM' is the right way to do it.

Any help would be much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-15 : 00:51:05

not sure you're using sql server as i see to_char. in sql server we will do it like below


SELECT TOP 1 date1 WITH TIES
FROM table
ORDER BY DATEADD(yy,1900-YEAR(datefield),datefield) ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-15 : 02:57:01

Assuming the notation MM=month and DD=day in your database (oracle I belive), you could do something like:


select date1
  from table
 where min(to_char(date1,'MMDD')=(select min(to_char(date1,'MMDD')
                                    from table               
                                 )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources