How to compare date in Oracle?

Asked 6/10, 2015 at 14:14 Answered 13/3, 2017 at 21:0

Solved sql oracle-database date-arithmetic

I'm having issues with what I assumed would be a simple problem, but googling isn't helping a great load. Possibly I'm bad at what I am searching for nether the less.

SELECT ORDER_NUMB, CUSTOMER_NUMB, ORDER_DATE
FROM ORDERS
WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';

It tells me I am using an invalid identifier. I have tried using MON instead of MMM, but that doesn't help either.

Unsure if it makes any difference but I am using Oracle SQL Developer.

Arana answered 6/10, 2015 at 14:14 Comment(6)

Why are you converting the date to a string to do the comparison? Why not just do WHERE ORDER_DATE = '2000-06-07'? – Ensiform 6/10, 2015 at 14:16

I do not recognize "format" as a function like your code is suggesting. You want TO_CHAR – Sancho 6/10, 2015 at 14:16

What is the datatype of order_date? If it's DATE then you need where order_date = to_date('07/06/2000', 'dd/mm/yyyy'). This assumes that there are no time elements stored in the order_date column (ie. that everything stored is at midnight). – Ridgley 6/10, 2015 at 14:19

Where in the Oracle manual did you find the format() function? – Tuscarora 6/10, 2015 at 14:21

Why did you accept a completely wrong answer? – Aloe 6/10, 2015 at 14:38

@Ensiform '2000-06-07' is a string, not a date. Your LHS is correct, however, you must use TO_DATE or ANSI Date Literal on the RHS – Aloe 6/10, 2015 at 14:40

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'.

Aloe answered 6/10, 2015 at 14:34 Comment(17)

@Wernfried Where did you see OP telling anything about the time portion? "ANSI Date Literal does not support any time fraction values, just dates Where did you see my answer not focusing on the usage of the ANSI date literal? I have clearly mentioned "When you are dealing only with date without the time portion, then better use the ANSI DATE Literal." – Aloe 6/10, 2015 at 14:43

@Wernfried Seems you did not properly read my answer. I explicitly mentioned in point 3, that "When you are dealing only with date without the time portion, then better use the ANSI DATE Literal.". So how is it wrong? I am open to your comments :-) – Aloe 6/10, 2015 at 14:49

Yes, I missed that, sorry. However WHERE ORDER_DATE = to_Date('07-JUN-2000','DD-MON-YYYY') relies on session NLS_DATE_LANGUAGE. – Jarnagin 6/10, 2015 at 14:51

@Wernfried No problem. The NLS dependency with TO_DATE is a different thing. ANSI Date literal is complete NLS independent as it follows a standard format. Anyway, I will edit my answer to add the NLS dependency with TO_DATE. – Aloe 6/10, 2015 at 14:53

where does the implicit conversion happen in TO_CHAR(dateField, '...dateFormat...') = 'dateString' ? You are comparing 2 strings here. – Sancho 6/10, 2015 at 15:11

@Sancho "where does the implicit conversion happen in TO_CHAR(dateField, '...dateFormat...') = 'dateString' ? You are comparing 2 strings here" The basic questions is, why would you do that? Where did you read that in the documentation? Do you have any proof? Did you test it? On what basis do you recommend it? – Aloe 6/10, 2015 at 15:14

@LalitKumarB: my dear friend, first of all you seem a little too much overwhelmed. At the end of this day, everything will remain the same, be it your point or whoever else that makes more sense. Back to our discussion, you may be knowledgeable in oracle, but seems you are way behind in text interpretation. If we are comparing 2 strings here - and we indeed are - unless you bring some groundbreaking proof we are not - then your heartened warning about implicit conversions does not even exist here in the first place. – Sancho 6/10, 2015 at 15:19

@Sancho I was just trying to help by sharing the knowledge. I have nowhere mentioned that data type conversion will happen within same data types. I have had been focusing on the importance on the usage of proper data types. Period. – Aloe 6/10, 2015 at 15:22

Well, again, you did receive reasons aside from me as to why one would choose to do that. And again, others also agree that nonetheless this has nothing to to with it being the best solution to the problem. All the urgency in the matter you started raising is completely out of place. You should have said "well, I think it's better to do this and this instead of that". And that's all. Afterwards you come asking for proofs when you yourself make false statements such as "there is an implicit conversion happening here". – Sancho 6/10, 2015 at 15:22

Your help is definitely valuable. I myself would suggest to just try to not inflate things much and go out of scope. – Sancho 6/10, 2015 at 15:24

@Sancho "out of scope" Nothing is out of scope. Everything is well documented in the fine manual. There is no way you can justify to compare strings while actually comparing dates in Oracle. – Aloe 6/10, 2015 at 15:33

@Sancho there are also performance issues. As you most probably do not have a function based index on expression to_date(...) then Oracle has nothing else to do, that use full table scan. The conversion to String and string based comparison will only work when using English locales. If user chooses different language settings, then your query will still be "correct", but will never return any data. – Icterus 6/10, 2015 at 20:17

So comparison TO_CHAR(ORDER_DATE, 'DD-MM-YYYY') = '07-06-2000'; will by just bad practice (and will not use index). While comparison TO_CHAR(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000'; is ticking bomb in the code, it will work until somebody changes session locales. – Icterus 6/10, 2015 at 20:38

@ibre5041: thanks for the concise and straight-to-the-point comments. I know learned from you that using a function will bypass the index (because the index column was indexed for a different type - I assume) – Sancho 7/10, 2015 at 8:23

@ibre5041: now, my questions on your statements: how is '07-06-2000' different than '07-JUN-2000' ? Both are string literals, aren't they? And if you have a conversion function that uses a fixed conversion format (like in your examples above) - how will it be setting-specific ? On the contrary, regardless of which NLS configurations you have locally or in the DB, since you are forcing the value to be in a given format X, you are assured you will be working with that format and that's it. Wrong ? – Sancho 7/10, 2015 at 8:26

It has nothing to do with the data type, but the values. the regular index on the column will store the column values in the index structure, while applying a function on the column would not hit the regular index as we are not looking for those indexed value anymore. Also, WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH'); will use the regular index on ORDER_DATE column. So, also much better in terms of performance. – Aloe 7/10, 2015 at 8:27

@Sancho you can believe that all the nations use the same year number and they also use 12 moths per year. But names of the differ, so for example JUN will be JUI in Frech or CER in Czech. So your query will still execute, but will never return any rows. – Icterus 7/10, 2015 at 8:41

try this:

SELECT ORDER_NUMB, CUSTOMER_NUMB, ORDER_DATE
FROM ORDERS
WHERE trunc(to_date(ORDER_DATE, 'DD-MMM-YYYY')) = trunc(to_date('07-JUN-2000'));

Spittoon answered 13/3, 2017 at 21:0 Comment(0)

-1

I do not recognize FORMAT as an oracle function.

I think you meant TO_CHAR.

SELECT ORDER_NUMB, CUSTOMER_NUMB, ORDER_DATE
FROM ORDERS
WHERE TO_CHAR(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';

Sancho answered 6/10, 2015 at 14:18 Comment(12)

Would it be better to convert the right side instead? (If ORDER_DATE is indexed...) – Sydel 6/10, 2015 at 14:19

A bigger question, though, is why do this at all? Why not just compare to the standard date format? – Threeply 6/10, 2015 at 14:19

As Siyual already suggested. Yes, better than what I am suggesting. I honestly have always did it this way so I am learning I could be simply comparing the date field right away. I guess many may end up preferring to convert to char because they do not know in which format the db stores its data - and do not want to bother in figuring it out (seems I belong to such group :-) – Sancho 6/10, 2015 at 14:21

This is completely wrong, and should always be avoided. Never ever compare string to a date. Always use TO_DATE or ANSI Date Literal to explicitly convert string into date. – Aloe 6/10, 2015 at 14:36

where does the implicit conversion happen in TO_CHAR(dateField, '...dateFormat...') = 'dateString' ? You are comparing 2 strings here. – Sancho 6/10, 2015 at 15:11

@Sancho Understand, dates and strings are not the same. You will fail each time you try to code/justify/advocate to do so. – Aloe 6/10, 2015 at 15:17

@LalitKumarB: here you go again :-) I still ask of you to show me where is this date value you are talking about in the results of a TO_CHAR() call and a string literal. – Sancho 6/10, 2015 at 15:25

@Sancho Why would you use TO_CHAR at all while comparing a date? It makes no sense. – Aloe 6/10, 2015 at 15:27

@Ververke - you are correct in that there is no implicit data conversion happening when you compare a date-as-a-string to another date-as-a-string. However, assuming that the column being compared with is a DATE, why would you want to bother converting it to a string in the first place? That's what Lalit is trying to get at, I think. – Ridgley 6/10, 2015 at 15:32

@Ridgley "That's what Lalit is trying to get at, I think." Indeed mate :-) – Aloe 6/10, 2015 at 15:37

@Boneist: that's a good point, and that has been addressed here by others as well. Question is if Lalit is correct when he says that there is no such a thing as NLS_Date_Language (or whatever the relevant parameter is) having effect in comparing a date field with a date literal value - or not. I am sure he is way more knowledgeable in oracle than I am, I just wanted that to be explicitly stated and resolved - specially because others (like me) used to think it was language/environment dependent. – Sancho 6/10, 2015 at 15:46

@Sancho Firstly, ANSI Date Literal is NLS independent, also, it has no time portion. Secondly, TO_DATE is prone to NLS settings, whcih I have already mentioned. Are you sure you are suggesting stuff on Oracle and not SQL Server? – Aloe 6/10, 2015 at 15:48

-2

try to_char(order_date, 'DD-MON-YYYY')

Eaves answered 6/10, 2015 at 14:19 Comment(12)

The better way is using to_date in parameter instead to_char in column. – Foust 6/10, 2015 at 14:23

Better use to_char(order_date, 'DD-MM-YYYY') = '07-06-2000' in order to be independent from session NLS_DATE_LANGUAGE. – Jarnagin 6/10, 2015 at 14:43

@Wernfried I would really be interested to see the proof. I have never ever heard of such a practice in real world. – Aloe 6/10, 2015 at 14:45

@LalitKumarB, Wernfried, OP compares a date in the sring format in the RHS and asks why it throws invalid identifier. I feel my answer clarifies the question – Eaves 6/10, 2015 at 14:45

@VinothS A date and string are completely different. If dates are stored as string, then it is a design issue.If you are comparing date with string then it is a code issue. In any case, it is simply wrong. – Aloe 6/10, 2015 at 14:51

@Wernfried By the way, please read the documentation about ANSI DATE LITERALS, and you will find that it is NLS independent. – Aloe 6/10, 2015 at 14:52

@Lalit Kumar B: There is nothing wrong with comparing strings containing date portions. It is often very readable even (e.g. to_char(mydate, 'yyyymm') = '201510'). Wernfried's comment is important, though: one should specify the date language when dealing with month names. So this is a correct answer to the question, only it could have been better :-) – Quintic 6/10, 2015 at 14:58

@ThorstenKettner You are missing the most important thing about the data type. When dealing with dates, especially while comparisons/arithmetic, you should leave the DATE as it is. Remember, *TO_CHAR is only for display purpose and not to be used while doing date arithmetic. – Aloe 6/10, 2015 at 15:1

@Lalit Kumar B: Well, why not? I get a parameter '07-JUN-2000' from somewhere. So I either use TO_CHAR on my column or TO_DATE on the parameter (plus TRUNC on my column possibly). Neither way is wrong, but yes, usually it's preferrable to change the string parameter to date rather than changing the date column to string. – Quintic 6/10, 2015 at 15:4

@ThorstenKettner "Well, why not?" If you get the date value as a parameter that too as a string, then you should use TO_DATE/ANSI Date literal whichever is applicable depending ont he time portion to explicitly convert string into date. Period. – Aloe 6/10, 2015 at 15:7

@ThorstenKettner Are you sure you are advocating your views for Oracle database? Because, what you are saying is a serious flaw in Oracle RDBMS. – Aloe 6/10, 2015 at 15:13

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags