Issue when comparing result of to_char(myDate, 'DAY') to a string
It is because day variable contains a blank padded value. Use trim function to get rid of leading and trailing spaces:
IF trim(day) = 'FRIDAY' THEN
checkFriday := 'Y';
END IF;
And please use VARCHAR2 datatype for string variables. Do not use VARCHAR.
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!
Filter by TO_CHAR() in the WHERE clause
The issue here is, that TO_CHAR(x,'DAY') is for Monday returning 'Monday ' with blanks in the end. According to documentation for TO_CHAR function:
The character elements MONTH, MON, DAY, and DY are padded with
trailing blanks to the width of the longest full month name, the
longest abbreviated month name, the longest full date name...
The correct result shall be returned if you do something like:
SELECT * FROM SS_EDGE_FORECAST ssef WHERE TO_CHAR(ssef.SS_TIMESTAMP, 'DAY') like 'MONDAY%'
Or even better, the TO_CHAR function has so-called format model modifiers, which you can use for removing these trailing blanks:
SELECT * FROM SS_EDGE_FORECAST ssef WHERE TO_CHAR(ssef.SS_TIMESTAMP, 'fmDAY') = 'MONDAY'
shall return desired output.
Documentation for TO_CHAR and format model modifiers here:
https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00216
to_char and to_date are returning different output
Try 'DD-MON-YY' format to map to '28-MAY-13'
'MM' maps to the month number.
EDIT: SQL can figure out to use the correct format in to_date; it automatically converts 'DD-MM-YY' to 'DD-MON-YY' when it sees the input string is in '28-MAY-13' format. to_char does not make any assumptions, however; so it is trying to compare '28-05-13' to '28-MAY-13'
EDIT2: An added note is that DUAL can only ever return one row; so you could just do
SELECT sysdate FROM DUAL
Dan Bracuk has some good points about date comparison; when possible keep them in date format. Use trunc(DateTime) if only the day matters and not the time; this is usually necessary if you use '=' but often not necessary if you do check for BETWEEN
SQL - How to answer this question : List employees who were hired on Tuesday and sort them from Z to A
alter session set nls_territory = 'AMERICA';
SELECT last_name, hire_date
FROM employees
WHERE TO_CHAR(hire_date, 'DAY') = 'TUESDAY'
ORDER BY last_name DESC;
Try with alter session
Name of day padded with spaces prevent proper behavior
The problem is with the DAY format. According to the date format documentation:
DAY
Name of day, padded with blanks to display width of the widest name of day in the date language used for this element.
To disable that behavior, you have to use the FM modifier:
FM
In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary.
In your particular case:
v_huidigeDag := to_char(v_mathDatum, 'fmDAY');
How to compare date in Oracle?
There are multiple issues related to your DATE usage:
WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';
- FORMAT is not an Oracle supported built-in function.
- Never ever compare a STRING with DATE. You might just be lucky, however, you force Oracle to do an implicit data type conversion based on your locale-specific NLS settings. You must avoid it. Always use TO_DATE to explicitly convert string to date.
WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');- When you are dealing only with date without the time portion, then better use the ANSI DATE Literal.
WHERE ORDER_DATE = DATE '2000-06-07';
Read more about DateTime literals in documentation.
Update
It think it would be helpful to add some more information about DATE.
Oracle does not store dates in the format you see. It stores it
internally in a proprietary format in 7 bytes with each byte storing
different components of the datetime value.
BYTE Meaning
---- -------
1 Century -- stored in excess-100 notation
2 Year -- " "
3 Month -- stored in 0 base notation
4 Day -- " "
5 Hour -- stored in excess-1 notation
6 Minute -- " "
7 Second -- " "
Remember,
To display : Use TO_CHAR
Any date arithmetic/comparison : Use TO_DATE
Performance Bottleneck:
Let's say you have a regular B-Tree index on a date column. now, the following filter predicate will never use the index due to TO_CHAR function:
WHERE TO_CHAR(ORDER_DATE, 'DD-MM-YYYY') = '07-06-2000';
So, the use of TO_CHAR in above query is completely meaningless as it does not compare dates, nor does it delivers good performance.
Correct method:
The correct way to do the date comparison is:
WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');
It will use the index on the ORDER_DATE column, so it will much better in terms of performance. Also, it is comparing dates and not strings.
As I already said, when you do not have the time element in your date, then you could use ANSI date literal which is NLS independent and also less to code.
WHERE ORDER_DATE = DATE '2000-06-07';
It uses a fixed format 'YYYY-MM-DD'.
Retrieving last monday but no data in result set
Used Trim before the statement and that resolved the issue.
trim(to_char(to_date(SYSDATE,'dd/mm/yyyy'), 'DAY')) = 'MONDAY'
Related Topics
How to Get Value Using Join Table with Different Values
How to Create Simple Fuzzy Search with Postgresql Only
Optimizing Delete on SQL Server
How to Remove White Space Characters from a String in SQL Server
How to Use a Package Constant in SQL Select Statement
How to Use Update Trigger to Update Another Table
What Is "Connect Timeout" in SQL Server Connection String
T-Sql: How to Create a Unique Key That Is Case Sensitive
How to Insert Identity Manually
Space Used by Nulls in Database
How Can This SQL Be Wrong? What am I Not Seeing
Check for Changes to an SQL Server Table
Re-Order Columns of Table in Oracle
Oracle: How to "Group By" Over a Range
Performance of SQL "Exists" Usage Variants