Date comparison returns unusual result - SQL Oracle
It works perfectly for me with proper use of TO_DATE and DATE values.
Never use TO_DATE on a DATE, It will implicitly convert it into string and then back to date using locale-specific NLS format.
'01/01/2015'is NOT a DATE, it is a STRING. You must use TO_DATE to explicitly convert it into DATE.
See what happens:
SQL> explain plan for select * from dual where to_date(sysdate) > to_date(sysdate -1);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3752461848
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(TO_DATE(TO_CHAR(SYSDATE@!))>TO_DATE(TO_CHAR(SYSDATE@!-1)))
14 rows selected.
SQL>
So, the actual filter applied is filter(TO_DATE(TO_CHAR(SYSDATE@!)) You will get incorrect output due to the implicit conversion based on locale-specific NLS format.
Anyway, now coming back to your original question.
For example,
Let's say your data looks like:
Setup:
SQL> CREATE TABLE t
2 (week_no VARCHAR2(2), long_week_no VARCHAR2(2), week_start_date DATE, week_end_date DATE, mnth VARCHAR2(3), yr VARCHAR2(4))
3 ;
Table created.
SQL>
SQL>
SQL> INSERT ALL
2 INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
3 VALUES ('1', '1A', TO_DATE('01/01/2015','DD/MM/YYYY'), TO_DATE('03/01/2015','DD/MM/YYYY'), 'JAN', '2015')
4 INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
5 VALUES ('1', '1B', TO_DATE('04/01/2015','DD/MM/YYYY'), TO_DATE('10/01/2015','DD/MM/YYYY'), 'JAN', '2015')
6 INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
7 VALUES ('2', '2', TO_DATE('11/01/2015','DD/MM/YYYY'), TO_DATE('17/01/2015','DD/MM/YYYY'), 'JAN', '2015')
8 INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
9 VALUES ('3', '3', TO_DATE('18/01/2015','DD/MM/YYYY'), TO_DATE('24/01/2015','DD/MM/YYYY'), 'JAN', '2015')
10 INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
11 VALUES ('51', '51', TO_DATE('20/12/2014','DD/MM/YYYY'), TO_DATE('26/12/2015','DD/MM/YYYY'), 'DEC', '2014')
12 SELECT * FROM dual
13 ;
5 rows created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
Table:
SQL> SELECT * FROM t;
WE LO WEEK_STAR WEEK_END_ MNT YR
-- -- --------- --------- --- ----
1 1A 01-JAN-15 03-JAN-15 JAN 2015
1 1B 04-JAN-15 10-JAN-15 JAN 2015
2 2 11-JAN-15 17-JAN-15 JAN 2015
3 3 18-JAN-15 24-JAN-15 JAN 2015
51 51 20-DEC-14 26-DEC-15 DEC 2014
SQL>
Query to filter the rows based on DATE range:
SQL> SELECT *
2 FROM t
3 WHERE To_date('15/01/2015', 'DD/MM/YYYY') BETWEEN
4 week_start_date AND
5 week_end_date;
WE LO WEEK_STAR WEEK_END_ MNT YR
-- -- --------- --------- --- ----
2 2 11-JAN-15 17-JAN-15 JAN 2015
51 51 20-DEC-14 26-DEC-15 DEC 2014
SQL>
Oracle SQL comparison of DATEs returns wrong result
WHERE to_char(REPORTDATE, 'DD.MM.YYYY')>'09.11.2013'
You are comparing two STRINGS. You need to compare the DATEs. As I already said in the other answer here, you need to leave the date as it is for DATE calculations. TO_CHAR is for display, and TO_DATE is to convert a string literal into DATE.
SELECT TO_CHAR(REPORTDATE, 'DD.MM.YYYY'),
COUNT(*)
FROM TABLE
WHERE REPORTDATE > TO_DATE('09.11.2013', 'DD.MM.YYYY')
GROUP BY TO_CHAR(REPORTDATE, 'DD.MM.YYYY')
Also, REPORTDATE is a DATE column, hence it will have datetime element. So, if you want to exclude the time element while comparing, you need to use TRUNC
WHERE TRUNC(REPORTDATE) > TO_DATE('09.11.2013', 'DD.MM.YYYY')
However, applying TRUNC on the date column would suppress any regular index on that column. From performance point of view, better use a Date range condition.
For example,
WHERE REPORTDATE
BETWEEN
TO_DATE('09.11.2013', 'DD.MM.YYYY')
AND
TO_DATE('09.11.2013', 'DD.MM.YYYY') +1
Compare dates from two queries PL/SQL
You can enumerate the timestamps you want in a CTE, then bring the table with a left join:
with cte (reading_date) as (
select date '2020-11-17' from dual
union all
select reading_date + interval '30' minute
from cte
where reading_date + interval '30' minute < date '2020-11-19'
)
select c.reading_date, d.reading_value
from cte c
left join dcm_reading d on d.reading_date = c.reading_date
order by c.reading_date
I like to use recursive queries rather than Oracle specific connect by syntax, because they are standard SQL - but that's mostly a matter of taste, the logic remains the same.
Oracle Date Comparison Issue Totally confused - what am I doing wrong?
to_date(SYSDATE, 'DD/MM/YYYY ') that is your problem. It gives 27.12.0013 as a result. That is why you get false printed. Actually you don't need to convert sysdate to date as it is already of type date. Use this instead:
secDate DATE := SYSDATE;
TO_CHAR returning incorrect results when comparing to date string in Oracle
I found the issue. Its nothing to do with NLS but rather with the column name im passing to the to_char. Instead of mydateI should've been substituting it with bd_date.
To show my working:
WITH dateparam
AS ( SELECT TRUNC (SYSDATE, 'YYYY') + LEVEL - 1 AS mydate
FROM DUAL
CONNECT BY TRUNC (TRUNC (SYSDATE, 'YYYY') + LEVEL - 1, 'YYYY') =
TRUNC (SYSDATE, 'YYYY'))
SELECT
mydate,
ADD_MONTHS (LAST_DAY (mydate) + 1, -1) bd_date,
TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') day_num,
CASE
WHEN TO_CHAR (mydate, 'DD.MM') IN ('01.01')
THEN
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
6
THEN
3
ELSE
1
END
ELSE
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
1
THEN
1
ELSE
0
END
END
add_days
/*ADD_MONTHS (LAST_DAY (mydate) + 1, -1)
+ CASE
WHEN TO_CHAR (mydate, 'DD.MM') IN ('01.01')
THEN
(DECODE (
(TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD')),
6, 3,
7, 2,
1, 1,
1))
ELSE
(DECODE (
(TO_CHAR (ADD_MON(LAST_DAY (mydate) + 1, -1), 'fmD')),
7, 2,
1, 1,
0))
END
date_calc*/
FROM dateparam where mydate <= '03JAN2020'
ORDER BY 1;
Yields:
MYDATE BD_DATE DAY_NUM ADD_DAYS
--------- --------- ------- ----------
01-JAN-20 01-JAN-20 4 1
02-JAN-20 01-JAN-20 4 0
03-JAN-20 01-JAN-20 4 0
3 rows selected.
The problem was in my original code:
WHEN TO_CHAR (mydate, 'DD.MM') IN ('01.01')
This does the calculation for 01.01 and 02.01 but the bd_date is the same for all the days in January. So once I do a distincton all the rows in January I will end up with the two rows as shown above, once I remove the column mydate.
The corrected comparison should be on bd_date instead:
WHEN TO_CHAR ( ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'DD.MM') IN ('01.01') --Get bd_date using mydate
Working code:
/* Formatted on 02.04.2020 10:42:53 (QP5 v5.163.1008.3004) */
WITH dateparam
AS ( SELECT TRUNC (SYSDATE, 'YYYY') + LEVEL - 1 AS mydate
FROM DUAL
CONNECT BY TRUNC (TRUNC (SYSDATE, 'YYYY') + LEVEL - 1, 'YYYY') =
TRUNC (SYSDATE, 'YYYY'))
SELECT DISTINCT
ADD_MONTHS (LAST_DAY (mydate) + 1, -1) bd_date,
TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') day_num,
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'DD.MM') IN
('01.01')
THEN
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
6
THEN
3
ELSE
1
END
ELSE
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
1
THEN
1
ELSE
0
END
END
add_days,
ADD_MONTHS (LAST_DAY (mydate) + 1, -1)
+ CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'DD.MM') IN
('01.01')
THEN
(DECODE (
(TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD')),
6, 3,
7, 2,
1, 1,
1))
ELSE
(DECODE (
(TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD')),
7, 2,
1, 1,
0))
END
date_calc
FROM dateparam
ORDER BY 1;
Results:
BD_DATE DAY_NUM ADD_DAYS DATE_CALC
--------- ------- ---------- ---------
01-JAN-20 4 1 02-JAN-20
01-FEB-20 7 2 03-FEB-20
01-MäR-20 1 1 02-MäR-20
01-APR-20 4 0 01-APR-20
01-MAI-20 6 0 01-MAI-20
01-JUN-20 2 0 01-JUN-20
01-JUL-20 4 0 01-JUL-20
01-AUG-20 7 2 03-AUG-20
01-SEP-20 3 0 01-SEP-20
01-OKT-20 5 0 01-OKT-20
01-NOV-20 1 1 02-NOV-20
01-DEZ-20 3 0 01-DEZ-20
12 rows selected.
I agree with all the other suggestions in this post that the original set of dates could and should have been reduced before doing my calculations, instead of fetching all calendar dates of the year. I just needed to understand what I could have done to fix my original code.
Thank you everyone for your contributions!
Comparing with date in Oracle sql
The problem is twofold. Firstly the dates almost definitely have time-components. to_date('06-MAR-11','DD-MON-YY') is equivalent to 2011/03/06 00:00:00. If you use the TRUNC() function you will be able to see everything for that day:
select *
from test_table
where trunc(creation_date) = to_date('06-MAR-11','DD-MON-YY');
I would not use the MON datetime format model. As I explain here it depends on your region and settings. It's safer to use a numeric month format model instead. Equally, always specify century as part of the year.
where trunc(creation_date) = to_date('06-03-YY11','DD-MM-YYYY');
Your second problem is almost definitely your NLS_DATE_FORMAT; it appears to not take into account the time, hence why you see 4 identical dates. This only governs the manner in which data is displayed not how it is stored.
You can change this using something like:
ALTER SESSION SET NLS_DATE_FORMAT = "DD/MM/YYYY HH24:MI:SS"
If I set up a test environment using the following:
create table test_table ( creation_date date );
insert into test_table values ( sysdate );
insert into test_table values ( sysdate - 0.01 );
alter session set nls_date_format = "YYYY/MM/DD";
You can see the data returned does not include time (though SYSDATE does):
SQL> select * from test_table;
CREATION_D
----------
2013/04/12
2013/04/12
Altering the NLS_DATE_FORMAT and performing the same SELECT, you now get a time component:
SQL> alter session set nls_date_format = "YYYY/MM/DD HH24:MI:SS";
Session altered.
SQL> select * from test_table;
CREATION_DATE
-------------------
2013/04/12 12:48:41
2013/04/12 12:34:17
Lastly, when trying to select today's date alone no rows will be returned:
SQL> select *
2 from test_table
3 where creation_date = to_date('20130412','yyyymmdd');
no rows selected
But, when using TRUNC() to compare on only the date portion of the field you get all your rows again:
SQL> select *
2 from test_table
3 where trunc(creation_date) = to_date('20130412','yyyymmdd');
CREATION_DATE
-------------------
2013/04/12 12:48:41
2013/04/12 12:34:17
To actually answer your second question, if you want unique dates you can re-use the TRUNC() function:
select distinct trunc(creation_date)
from test_table
Related Topics
Tsql Select into Temp Table from Dynamic SQL
Convert Postgres Geometry Format to Wkt
How to Sort in Order as Entered in SQL Server
SQL Efficient Way to Join a Table Where All Values Exist
How Does Mongodb Avoid the SQL Injection Mess
How to Get the Difference Between Two Dates Rounded to Hours
Using If Else in SQL Select Statement
What Is the SQL Command to Return the Field Names of a Table
Group Consecutive Rows of Same Value Using Time Spans
How to Pass Table Name as a Parameter in Oracle
Return a Query from a Function
Why Does the Following Join Increase the Query Time Significantly
How to Add a Column That Doesn't Allow Nulls in a Postgresql Database
Generate Insert SQL Statements from a CSV File
Does the Order of Fields in a Where Clause Affect Performance in MySQL