query and date format

[PaulH]

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

[vnktummala]

set the nls_date_format for the session and try the query.

alter session set nls_date_format='YYYYMMDD';

Thanks,

[PaulH]

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.

[stecal]

Truncate the dates. That sets time back to midnight, all times are the same within a date.

[JMac]

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.

[PaulH]

Thanks for the example it works great