-
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
-
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|