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
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>
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!
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;
Oracle SQL - date comparison does not work
Assuming schedfinish is a date datatype, you can do this:
where schedfinish >= trunc(current_date)
and schedfinish < trunc(current_date + 2)
Getting Incorrect results for selecting values between 2 dates
You are comparing dates with strings; the dates are converted to strings to do that comparison. From the documentation:
During
SELECT FROMoperations, Oracle converts the data from the column to the type of the target variable.
When your dates are implicitly converted to strings, they all fall within that range of string values, based on the character value comparison rules.
The above isn't true of course; this supersedes it:
When comparing a character value with a
DATEvalue, Oracle converts the character data toDATE.
So something odd is happening in that implicit conversion, which will depend on your NLS_DATE_FOPRMAT setting. Anyway, the following advice still stands...
Compare dates with dates instead, either converting your strings with to_date() or with date literals:
where open_time >= date '2020-01-01' and open_time < date '2021-01-01'
You don't need to truncate the dates back to midnight; which will help with being able to use an index, if appropriate.
oracle sql date comparison doesn't work as expected
Oracle DATE columns contain a time as well (despite their name). Your existing rows probably have a time different than 00:00:00 (which is "assigned" to the date you create with the to_date() function).
You need to "remove" the time part of the column using trunc()
AND NOT (trunc(X.INSERT_DATE) = to_date('2013-01-17', 'yyyy-mm-dd'))
although I'd prefer to use <> instead of the NOT operator:
AND (trunc(X.INSERT_DATE) <> to_date('2013-01-17', 'yyyy-mm-dd'))
(but that is just a personal preference. I think it makes the condition easier to read).
So your complete statement would be:
SELECT insert_date
FROM X
WHERE trunc(X.INSERT_DATE) <> to_date('2013-01-17', 'yyyy-mm-dd')
Oracle - wrong result using sysdate
The where clauses of the queries are not identical.
The first query tries to match snap_date against the current date and time. This is not likely to succeed, unless a snap was created at the very same second when the query is executed.
The second query compares the date part snap_date against the current day (the time part is not taken into account).
This:
where to_char(snap_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
Actually means:
where trunc(snap_time) = trunc(sysdate)
Which can, (and should) be optimized as the clearer and more efficient:
where snap_time >= trunc(sysdate)
Related Topics
SQL Update All Values in a Field with Appended String Concat Not Working
Move SQL Server 2008 Database Files to a New Folder Location
Check If Entry in Table a Exists in Table B
How to Get the Active Users Connected to a Postgresql Database via SQL
How to Rollback or Commit a Transaction in SQL Server
Insert Data into Temp Table with Query
How to Convert a Database Row into a Struct
From a Sybase Database, How to Get Table Description ( Field Names and Types)
How to Avoid Nulls in My Database, While Also Representing Missing Data
Best Practice on Users/Roles on SQL Server for a Web Application
Generate a Random Number in the Range 1 - 10
Printing a Value of a Variable in Postgresql
What Is Rows Unbounded Preceding Used for in Teradata
Not Deferrable Versus Deferrable Initially Immediate
How to Monitor and Log Actual Queries Made Against an Access Mdb
How to Get Count() and Rows from One SQL Query in SQL Server