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:	10245

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:	Silver
Points:	172

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"

#765101

10 Mar 2016 02:49

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

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:	21314

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

About dotnetspider.com

Quick Links

General

Services