SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Finding earliest date - ignoring year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Speck
Starting Member

1 Posts

Posted - 09/15/2012 :  00:14:34  Show Profile  Reply with Quote
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.

Edited by - Speck on 09/15/2012 00:15:45

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts

Posted - 09/15/2012 :  00:51:05  Show Profile  Reply with Quote
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/

Go to Top of Page

bitsmed
Yak Posting Veteran

Denmark
98 Posts

Posted - 09/15/2012 :  02:57:01  Show Profile  Reply with Quote
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               
                                 )

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000