CODEWITHSUNDEEP
sqloracle-databasedateto-char
Nvr
Nvr

Reputation: 171

Difference in TO_CHAR format

The query below is being used to get a count of records between some days. Actually, there are no records for that particular date so query should return 0. But due to some mistake, it returns the value incorrectly.

SELECT COUNT(*)
FROM transaction_tb 
WHERE STATUS NOT IN ('Wrong', 'Dont') 
AND to_char(date, 'DD-MM-YYYY') BETWEEN '01-04-2019' AND '31-03-2020';

But when trying with the below query, it returned 0 as excepted.

SELECT COUNT(*) 
FROM transaction_tb 
WHERE STATUS NOT IN ('Wrong', 'Dont') 
AND to_char(date, 'YYYYMMDD') BETWEEN '20190401' AND '20200331';

Both queries should return the same value.
What is the difference between these queries?

Upvotes: 0

Views: 257

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

When you convert to a character/text type, you're doing a text comparison for the BETWEEN operation. It's not doing a date check anymore at all. Text comparisons are alphabetical order, character by character, and stop as soon as anything is out of the range.

With that in mind, looking at the first sample:

to_char(date, 'DD-MM-YYYY') BETWEEN '01-04-2019' AND '31-03-2020'

The second character on both boundaries is a 1, so anything that doesn't have a 1 for the second character will fail. In other words, the only dates that can possibly succeed past this point are the 1st, 11th, 21st, and 31st days of the month.

Going further we get to the 4th character, which is 0 for both boundary literals. Nothing with a 1 can pass this example, including all dates from October through December.

Next up is the 5th character, which is 4, and 3, in that order. If we ignore the order issues, which excludes everything, and just think in terms of the 3-4, this excludes any date from January and February, as well as any date after April.

Now let's look at the second example. The one that seems to work:

to_char(date, 'YYYYMMDD') BETWEEN '20190401' AND '20200331';

Applying the same process, we get valid results all the way through the year portion. But then we get to the 0 in 04 and 03 and we have the same problem as before, with not counting October through December. Finally, we also have the same problem on the last character, which limits you to the 1st, 11th, 21st, and 31st days of the month.

What you should do is keep the comparisons as a date. If you ever find yourself converting to a string type for date check, you're making a big mistake.

Instead, you want to represent your boundaries as date literals:

"date" BETWEEN DATE '2019-04-01' AND DATE '2020-03-31'

Upvotes: 1

JonathanDavidArndt
JonathanDavidArndt

Reputation: 2732

Everything in the other answers is good, and very true (always use dates to compare with dates, don't convert to strings).

In addition, when you are only concerned with Y-M-D (and the time-of-day is not important), you should also incorporate TRUNC which will just remove the H-M-S so that time-of-day does not become an issue:

WHERE TRUNC( TO_DATE( '01-04-2019', 'MM-DD-YYYY' ) ) <= TRUNC( date_column )
  AND TRUNC( date_column ) <= TRUNC( TO_DATE( '03-30-2020', 'MM-DD-YYYY' ) )

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Don't use strings for date comparisons!

SELECT COUNT(*)
FROM transaction_tb 
WHERE STATUS NOT IN ('Wrong', 'Dont') AND
      date >= DATE '2019-01-04' AND
      date <= DATE '2020-03-31'

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

If you compare two dates, you get date comparison semantics. If you compare two strings, you get string comparison semantics which is alphabetical order. The string "10-10-1950" comes alphabetically between the string "01-04-2019" and the string "31-03-2020". Despite the fact that the date it represents is obviously much earlier than either of the dates represented by the other strings.

If you compare dates to dates, you get date comparison semantics which is what you want. Either use date literals

where date_column between date '2019-01-04' and date '2020-03-31'

or use a to_date to convert your strings to dates

where date_column between to_date( '01-04-2019', 'MM-DD-YYYY' ) 
                      AND to_date( '31-03-2020', 'MM-DD-YYYY' )

Upvotes: 3

Related Questions