compare date problem RRS feed

  • Question

  • User-804681621 posted
    How can I compare date in oracle.
    I have table with datatype as date. And a function to return date. But I don't know why I can't get the right date after comparing the date. Below is my sql.

    Select to_char(expiry_date,'ddmonyyyy'), to_char(function_returndate,'ddmonyyyy') from table where to_char(expiry_date,'ddmonyyyy')>=to_char(function_returndate,'ddmonyyyy') ;

    Below is result selected.
    Expiry date function_returndate
    31Jan2016 30Jun2016

    Why I get the expiry date which is smaller than return date from the above sql?
    Wednesday, October 12, 2016 8:08 AM

All replies

  • User-804681621 posted
    Anyone knows?
    Wednesday, October 12, 2016 9:58 AM
  • User753101303 posted


    Try https://social.msdn.microsoft.com/Forums/sqlserver/en-US/07a3d19f-92ef-42ad-8199-d008471ea805/passing-date-parameter-to-oracle-query?forum=sqlreportingservices

    That is you'll use a :name placeholder inside your query and you'll pass a date value from the client side. For now as you are using to_char you likely end up in comparing strings (and 30Jun2016 comes before 31Jan2016 in the alphanumeric order plus it is likely less efficient).

    I'm using SQL Server but it should be the same principle with most if not all DBMS (ie use parameters in SQL queries and feed those parameters with the actual typed value).

    Edit: similarly formating is best done client side. This way you'll have dates on the client side that could process as such and possibly show differently depending on the connected user. Else you'll just have strings. In short you should work as often as possible with actual values rather than with their string representation.

    Wednesday, October 12, 2016 10:58 AM
  • User-804681621 posted
    How can I format a date value and perform comparison.
    Wednesday, October 12, 2016 11:30 AM
  • User753101303 posted

    The point is to not format yourself the value.

    ie you would have WHERE YourDateColumn<=:YourValue in your SQL string and you feed the value from the client side using :

    cmd.Parameters.Add("YourValue", OracleDbType.Date).Value = YourDateTimeValue; // not a string but a DateTime

    ADO.NET will just just do the right thing. Here it is a bit unclear which values are passed from the client side. If none, what if you try to just compare values without using TO_CHAR ie just :

    where expiry_date>=function_returndate

    What happens then? The basic idea is to work with values as much as possible without going through a value to string conversion. For now you are wokring with string values which is likely what causes your issue (as ddmonyyyy strings are not sorted the same way than the corresponding actual date values). 

    So for now I would try :

    Select expiry_date, function_returndate from table where expiry_date>=function_returndate ;

    Not sure if you have just that and what is function_returndate (this is actually just a column in which you stored the result of a previosuly called function ????)

    Wednesday, October 12, 2016 12:00 PM
  • User2117486576 posted

    Oracle will compare date type objects.  If you have two dates (date type) then date1 > date2 will compare correctly.

    If you have a varchar and you want to compare to a date, use the built in TO_DATE() function to convert to date before comparing.


    If you must convert dates to varchar before comparing then convert to 'yyyymondd'.  The the lexicographic comparison should return the same value as the date comparison.

    Wednesday, October 12, 2016 12:05 PM
  • User223512702 posted

    Hi Guys, I think it will work i think so use below conditions i tried to used in Var Condition

    var t1 = new Date(2013, 4, 30, 16, 5)
    var t2 = new Date(2013, 4, 30, 16, 5)
    t1 == t2 // => false
    t1 === t2 // => false
    t1.getTime() === t2.getTime() // => true

    Wednesday, January 11, 2017 1:18 PM