You must Sign In to post a response.
  • Category: SQL Server

    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
    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
    Select min(cast (start_date) as datetime),
    min(cast(end_date) as datetime)
    from table1

  • #765099
    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
    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
    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]


Sign In to post your comments