Re: Date comparison

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: davidsharples@xxxxxxxxx
  • Date: Mon, 12 Sep 2005 13:05:53 +0200

This probably needs some qualifying. Whenever you convert a string to a date, use an explicit conversion and an explicit format. The default format can be changed without notice in the init.ora file. It can also be changed on the client side. Note also that there is a strong relationship between date format and language, so you should also be explicit about the language that you are using as well (a typical example is a test for "sunday". to_char('11-SEP-2005', 'DAY') will not return 'SUNDAY' if the language is anything else than English, so your to_char should include a third parameter to force the language).

Concerning TRUNC(), always apply it to constants, never to columns if you have any consideration for your indexes.

Stephane Faroult

David Sharples wrote:

yes, compare dates to date and you can;t go wrong. If you just want the date part use trunc()

On 9/12/05, *manoj.gurnani@xxxxxxxxxxxxx <mailto:manoj.gurnani@xxxxxxxxxxxxx>* <manoj.gurnani@xxxxxxxxxxxxx <mailto:manoj.gurnani@xxxxxxxxxxxxx>> wrote:

Hi All, I've a date column in a oracle db table which stores date and time values. Is it advisable to use a date variable directly in a comparison with a date column without any formatting or using any other conversion functions like to_char.


Other related posts: