Can MIN() and MAX() functions be used with Date columns in Oracle SQL

(126 Views)


Many SQL novice programmers have doubt whether MIN() and MAX() function can be used on column having datatype as 'date'. The answer is Yes.

Yes, you can use MAX() and MIN() function for dates, what this function does it returns either oldest date or latest date depending on whether you use MIN() or MAX() n your query

MIN() and MAX() function on Date Example

  1. Table:
  2. CREATE TABLE Product ( PRODUCT_ID NUMBER, MFG_DATE DATE, EXPIRY_DATE DATE, PRICE_VALUE NUMBER );

  3. Insert Data into Product Table
  4. INSERT INTO Product values (1, to_date('21-JAN-2018','DD-MON-YYYY'),to_date('27-JAN-2018','DD-MON-YYYY'), 100); INSERT INTO Product values (2, to_date('28-JAN-2018','DD-MON-YYYY'),to_date('15-FEB-2018','DD-MON-YYYY'), 120); INSERT INTO Product values (3, to_date('01-JAN-2018','DD-MON-YYYY'),to_date('04-JAN-2018','DD-MON-YYYY'), 150); INSERT INTO Product values (4, to_date('05-JAN-2018','DD-MON-YYYY'),to_date('11-JAN-2018','DD-MON-YYYY'), 120); INSERT INTO Product values (5, to_date('12-JAN-2018','DD-MON-YYYY'),to_date('20-JAN-2018','DD-MON-YYYY'), 100);

  5. Select * from Product
  6. PRODUCT_IDMFG_DATEEXPIRY_DATEPRICE_VALUE
    121-JAN-2018
    27-JAN-2018100
    228-JAN-201815-FEB-2018120
    301-JAN-201804-JAN-2018150
    405-JAN-201811-JAN-2018120
    512-JAN-201820-JAN-2018100

  7. MAX() function on MFG_DATE:
  8. SELECT MAX (MFG_DATE) AS "Max Date" FROM Product;

    Output:

    Max Date --------- 28-JAN-2018

  9. MIN() function on MFG_DATE:
  10. SELECT MIN (MFG_DATE) AS "Min Date" FROM Product;

    Output:

    Min Date --------- 01-JAN-2018

Solution Worked 0 UpvotesUpvote

        

Solution Didn't Worked 0 DownvotesDownvote



Comments



Search