Printable View
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
set the nls_date_format for the session and try the query.
alter session set nls_date_format='YYYYMMDD';
Thanks,
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.
Truncate the dates. That sets time back to midnight, all times are the same within a date.
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.
Thanks for the example it works great