CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005t-sql
Tanya
Tanya

Reputation: 1621

Find out the Old Date from a date column in sql

How the find the oldest values from the datetime column?

I have table with datetime column (UpdateDate), and i need to find out the oldest data based on the UpdateDate .

Id     UpdateDate                   Desc
-----------------------------------------
1    2010-06-15 00:00:00.000       aaaaa
2    2009-03-22 00:00:00.000       bbbbb
3    2008-01-12 00:00:00.000       ccccc
4    2008-02-12 00:00:00.000       ddddd 
5    2009-04-03 00:00:00.000       eeeee
6    2010-06-12 00:00:00.000       fffff

I have Find out the old year dates from the current date using Select UpdateDate from Table1 where DATEDIFF(YEAR,UpdateDate,getdate()) > 0 Query. But I need to find out the 2008th data only (Since the 2008 is the oldest one here) I dont know what is there in the Table I need find out the Oldest date values.. How is it Possible?

Upvotes: 1

Views: 4172

Answers (4)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

If you want the data that within 2008 year try this:

Select UpdateDate From Table1 
Where Year(UpdateDate) = 
(
   Select Year(UpdateDate) 
   from Table1 Order By UpdateDate ASC Limit 1
) ;

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

You can use top and order by.

select top(1) UpdateDate
from Table1
order by UpdateDate

Update:

If you want all rows for the first year present you can use this instead.

select *
from (
      select *,
             rank() over(order by year(UpdateDate)) as rn
      from Table1
     ) as T
where T.rn = 1

Upvotes: 0

dotnetstep
dotnetstep

Reputation: 17485

Select UpdateDate from Table1 where DATEDIFF(YEAR,PartDateCol,getdate()) IN
(Select MAX(DATEDIFF(YEAR,PartDateCol,GETDATE())) DiffYear from Table1)

This will return two record of 2008. If your records has four 2006 date than it return all 2006 data if difference is large.

Upvotes: 3

Uri Goren
Uri Goren

Reputation: 13700

One way of doing this is

Select UpdateDate from Table1 where YEAR(UpdateDate )=2008

But, you can find out the oldest dates by ordering the data as such

Select * from Table1 order by UpdateDate ASC

Upvotes: 0

Related Questions