Get min date by compare 2 Row

Category: SQL Server
10 Mar 2016 01:48
Joined: 12/02/2013
Level: Silver
Points: 147
Points: 5
Get min date by compare 2 Row
Hi friends,

select min(START_DATE,END_DATE) from TABLE1
where (( START_DATE like '%APR%') or (START_DATE like '%MAY%'))
and END_DATE like '%MAY%';

START_DATE END_DATE
------------------ ----------------
29-JAN-16 01-FEB-16
26-FEB-16 29-FEB-16
01-FEB-16 29-FEB-16

In the above table i have to find the Minimum date from the 2 rows, Have to compare the 2 row.

I need the ans as 29-JAN-16,.

Have to do(modify) in the same above Query.

Any Idea?

Thanks in advance.
#765097
10 Mar 2016 01:56
Joined: 05/06/2009
Level: Gold
Points: 10218
Points: 3
I am not clear about the requirement.
As per my understanding. For the above data you want only one date date, that should be minimum of Start_date or End_date for the entire data right?

Try the following query
select top 1 * from (select min(START_DATE,END_DATE) as Mdate from TABLE1 where (( START_DATE like '%APR%') or (START_DATE like '%MAY%')) and END_DATE like '%MAY%') as aa order by Mdate ;
By Nathan
Direction is important than speed
#765098
10 Mar 2016 01:58
Joined: 13/01/2015
Level: Bronze
Points: 57
Points: 1
Select min(cast (start_date) as datetime),
min(cast(end_date) as datetime)
from table1
#765099
10 Mar 2016 02:16
Joined: 12/02/2013
Level: Silver
Points: 147
Points: 1
Hi Nathan,

Thank you.

Sorry am trying it in ORACLE.

There it shows error in "top 1"

ERROR:
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"

Joined:	12/02/2013
Level:	Silver
Points:	147

Points:	5

Joined:	05/06/2009
Level:	Gold
Points:	10218

Points:	3

Joined:	13/01/2015
Level:	Bronze
Points:	57

Points:	1

Joined:	12/02/2013
Level:	Silver
Points:	147

Points:	1

#765101

10 Mar 2016 02:49

Joined:	05/06/2009
Level:	Gold
Points:	10218

Points:	2

Can you try this


select min(Mdate) from
(select min(START_DATE,END_DATE)  as Mdate from TABLE1 
where (( START_DATE like '%APR%') or (START_DATE like '%MAY%')) 
and END_DATE like '%MAY%') as aa

By Nathan
Direction is important than speed

#765105
11 Mar 2016 02:14
Joined: 13/06/2007
Level: Gold
Points: 20603

Points: 2
do not use LIKE for date, better to use BETWEEN or IN query, see below query snippet
SELECT MIN(mydate) mindate, MAX(mydate) maxdate
FROM(
SELECT expenseDate AS mydate
FROM tableA
UNION ALL
SELECT invoicedate AS mydate
FROM tableB
) AS table
Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

Get min date by compare 2 Row

Email subscription