query and date format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: query and date format

  1. #1
    Join Date
    Feb 2009
    Posts
    91

    query and date format

    I have date column that contains date and time. I want to only get back
    distinct dates in YYYYMMDD format.

    I start with alter session set nls_date_format = 'YYYYMMDD'

    When I run this query below
    select distinct(create_date) from table_A partition (BLA)

    I am getting back more than one row as shown below. What do I have to do to just one row per YYYYMMDD

    CREATE_D
    --------
    20101021
    20101021
    20101021
    20101021

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    set the nls_date_format for the session and try the query.

    alter session set nls_date_format='YYYYMMDD';

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Feb 2009
    Posts
    91
    As you can see from my first post I mentioned I set the nls_date_format.

    I think because the column also contains time I need to do substr or something to exclude the time from the column.

    As mentioned i only want to match the YYYYMMDD part and don't care
    about the time. This way I will get back one row for every distinct case
    as opposed to getting back several rows for the same date.

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Truncate the dates. That sets time back to midnight, all times are the same within a date.

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    NLS_DATE_FORMAT only reformats how the date is displayed. Underneath - it's still in the Oracle datetime format.

    Example:

    SQL> SELECT * FROM john_date;

    DATE1
    ---------
    27-OCT-10
    26-OCT-10
    23-OCT-10

    3 rows selected.

    SQL> ALTER SESSION SET nls_date_format = 'DD MM YY';

    Session altered.

    SQL> SELECT * FROM john_date;

    DATE1
    --------
    27 10 10
    26 10 10
    23 10 10

    3 rows selected.

    SQL> SELECT sysdate FROM dual;

    SYSDATE
    --------
    27 10 10

    1 row selected.

    SQL> SELECT * FROM john_date WHERE date1 = SYSDATE;

    no rows selected

    SQL> REM - Because SYSDATE has moved on since the first record was created using 'SYSDATE' ...
    SQL>
    SQL> REM - So '27 10 10' and SYSDATE are NOT the same as the time portions still exist (they're just

    SQL> REM - not displayed ...
    SQL>
    SQL>
    SQL> SELECT * FROM john_date WHERE TRUNC(date1) = TRUNC(SYSDATE);

    DATE1
    --------
    27 10 10

    1 row selected.

    SQL> REM - This works because TRUNC has stripped away the time portion.
    SQL>
    SQL> REM - Similarly ...
    SQL>
    SQL>
    SQL> SELECT * FROM john_date
    2 WHERE TO_CHAR(date1,'DDMMYYYY') = TO_CHAR(sysdate,'DDMMYYYY');

    DATE1
    --------
    27 10 10

    1 row selected.

    SQL> REM - This also works because we are comparing 'like with like' there no time portions ...
    SQL>
    SQL>
    SQL> REM - Whereas including times ...
    SQL>
    SQL>
    SQL> SELECT * FROM john_date
    2 WHERE TO_CHAR(date1,'DDMMYYYYHH:MM') = TO_CHAR(sysdate,'DDMMYYYYHH:MM');

    no rows selected

    SQL> REM - fails.

  6. #6
    Join Date
    Feb 2009
    Posts
    91
    Thanks for the example it works great

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width