CODEWITHSUNDEEP
sqlsql-serverdate
RookieAppler
RookieAppler

Reputation: 1545

Earliest and Lastdate for each year in sql

I have a column with 3 columns. I have multiple records for a year. As you see some of my records as follows

 ID stardate  enddate
 1 1/1/2010   5/3/2010
 2 2/4/2010   NULL -**EDIT**
 3 1/2/2011   5/6/2011
 4 3/4/2011   NULL -**EDIT**

I want to get a result for the earliest date in that year and the last date in that year. So output could be like

 **EDITED:**     1/1/2010   12/31/2010 - For Year 2010
 **EDITED:**     1/2/2011   12/31/2011 - For Year 2011

How can i get that in a query?If you need more info,please ask. Thanks

EDIT: If for the year if one of the columns read NULL then I have to consider the last day of the year as the enddate. i.e.12/31/YYYY. And I need to do that for each year again.

Upvotes: 1

Views: 70

Answers (2)

mnshahab
mnshahab

Reputation: 780

select MIN(stardate),max(enddate)
from [Tablename]
where YEAR(enddate)=2013

Upvotes: 1

s1lence
s1lence

Reputation: 2188

Assuming you use DATE (or related) columns in a MySQL table, something like this should serve your request:

SELECT   MIN(startdate), 
         MAX(enddate), 
         YEAR(startdate) 
FROM     my_table 
GROUP BY YEAR(startdate);

This groups all entries by year (of the startdate) and show you the minimum and maximum entries for each year as you want. See also the documentation for the DATE function in MySQL.

There are similar date functions and possibilities if you are using an other database system. Usually you can easily find them by googling the database system and something like "date functions".

Upvotes: 2

Related Questions