CODEWITHSUNDEEP
sqloracle-databasedate
Omnus Ruthius
Omnus Ruthius

Reputation: 120

Oracle SQL Data between two Dates

I'm looking at some SQL and trying to understand what it's trying to do. I was told it returns data between yesterday at midnight and today. Can someone translate the follow excerpt into plain English for me?

SELECT ...
FROM ...
WHERE CREATEDATE >=TO_DATE(TRUNC(SYSDATE-1) || ' 00:00:00', 'DD-MON-YY HH24:MI:SS')
AND CREATEDATE <=TO_DATE(TRUNC(SYSDATE-1) || ' 23:59:59', 'DD-MON-YY HH24:MI:SS')

This doesn't seem to work unless I change the bottom line to:

AND CREATEDATE <=TO_DATE(TRUNC(SYSDATE) || ' 23:59:59', 'DD-MON-YY HH24:MI:SS')

Any help with understanding this code will be appreciated.

Upvotes: 0

Views: 538

Answers (2)

spencer7593
spencer7593

Reputation: 108500

The predicates look way more complicated than they need to be.

Why wouldn't this return the result you are looking to return? 

   WHERE CREATEDATE >= TRUNC(SYSDATE-1)
     AND CREATEDATE <  TRUNC(SYSDATE)

To explain that in "plain english", all three of the expressions returned by this SELECT statement:

 SELECT SYSDATE
      , TRUNC(SYSDATE)
      , TRUNC(SYSDATE-1)
   FROM DUAL

return DATE values (values that are of Oracle datatype DATE).

  -------------------  -------------------  -------------------
  2016-04-16 13:59:26  2016-04-16 00:00:00  2016-04-15 00:00:00

What we're not seeing is the datatype of CREATEDATE. Obviously, we expect it to be defined as DATE. But if you've declared it as VARCHAR and are storing strings in format like '16-APR-2016 13:59:26' then "doesn't seem to work unless" issue you are observing is just the tip of an iceberg.

(The behavior you are observing can easily be explained if CREATEDATE is declared as VARCHAR rather than DATE.)

If your SQL is working as it is written, it's dependent on the current setting of NLS_DATE_FORMAT. That is, your SQL could be easily broken with a relataively innocuous statement, such as:

 ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD' ;

A rundown of some of the components of the expressions in your current query...

  • SYSDATE returns the current date and time as a DATE

  • SYSDATE-1 subtracts 1 day from the current date and time and returns a DATE

  • TRUNC(<dateexpr>) "lops off" the time component from a DATE value, returning just the date portion with time set to midnight

  • || is the string concatenation operator

(NOTE: performing string concatenation with a DATE expression requires Oracle to perform an implicit TO_CHAR conversion. Oracle use NLS_DATE_FORMAT as the default format model.)

  • ' single quotes enclose string literals

The second argument of the TO_DATE function is a format model.

  • DD is two digit day (e.g. 07 or 16)
  • MON is three letter abbreviation for the month
  • YY is two character representation of the year, with implied century (
  • HH24 is two digit hour (24 hour clock), 00 thru 23
  • MI is two digit minutes, 00 thru 59
  • SS is two digit seconds, 00 thru 59

(NOTE: Didn't we already fix the Y2K problem? Why are we re-creating it?)

I think the most important things to understand about the current SQL: It's using expressions that are more convoluted than is required. And I see the expressions as being broken in three ways:

  • dependent on NLS_DATE_FORMAT for the implicit TO_CHAR function to appropriately match the explicit format model in the the TO_DATE function

  • no excuse for using a two character year where we could easily use four digit year. Especially when there's no need to convert DATE values to strings in the first place.

  • using a <= comparison rather than a < comparison, depending on the maximum precision of a datetime type to be one second, when we can just as easily specify "less than" midnight of the following day, with a pattern that handles fractional seconds.

If CREATEDATE is declared as VARCHAR, it's broken in another way... the comparisons are being performed as string comparisons. There's an implicit TO_CHAR conversion for the expressions on the right side of the comparisons. And those conversions are (again) dependent on NLS_DATE_FORMAT setting. With the age-old Oracle default setting of DD-MON-YY, the string comparisons will work in a sense that it's not invalid syntax, but in terms of returning date values that are between two other date values, those comparisons are broken.

Upvotes: 5

evenro
evenro

Reputation: 2646

So what's going on is kind of weird. The conversions here are a bit confusing, but let's dig into it.

TRUNC(SYSDATE-1) is translated to a VARCHAR2 in this case, and based on the default NLS parameters, is translated to the date of yesterday.
Today it's 4/18/2016, so it would just return the string '17-APR-16' (based on the default NLS format).

Next, you concatenate to this string, the "time" string - 00:00:00.
this builds the string '17-APR-16 00:00:00' - midnight of yesterday.
This is wrapped up in "To_date" with a format of 'DD-MON-YY HH24:MI:SS' which makes it an actual date (the dataDATE).

The second date which is constructed is Yesterday's last minute - '17-APR-16 23:59:59' ...

So you are looking for all the records which were created yesterday in this query.

It's a bit confusing since the "TRUNC" of a date returns a date, but the concatenation to a string forces an implicit conversion.

Let me know if you have more questions about what's going in there.

P.S. I would recommend to rewrite the query with only dates, since the query will fail with different NLS settings...

Upvotes: 2

Related Questions