10 Replies Latest reply: Aug 15, 2008 11:36 AM by 655078 RSS

    Comparing date and string with SQL where clause

    655078
      Hi,

      I have a column which stores date as VARCHAR2(23) in YYYY/MM/DD-HH:MM:SS:LLL format. I store another date as string in YYYY/MM/DD format in a string variable in my program. I need to compare these two dates with sql where clause.

      Column in the table - TransferredDate - 2007/12/14-01:52:30:098
      My string variable - sDate - 2008/08/10

      I understand that storing date as VARCHAR2(23) is a bad design, but unfortunately I don't have control over the database.

      For equality, the following works - where TransferredDate like sdate||'%'

      But I don't get the correct results for "greater than" queries -

      select Transferreddate from table where Transferreddate > '2008/08/10';

      In the result I get all the dates greater than 2008/08/10 including 2008/08/10.

      My problem is how do I cmpare two dates which are stored as strings using a sql where clause. Since I use proprietary programming language, I cannot make use of Oracle functions like to_date() etc. I can only use the where clause.

      Any help would be appreciated.

      Message was edited by:
      user652075
        • 1. Re: Comparing date and string with SQL where clause
          Keith Jamieson
          So the problem is that you are getting 2008/08/10 and you want all dates after it.
          Use >=2008/08/11.

          Dates without a time are midnight. so 20080810 is actually 20080810000000

          Dates on same day with a time will be greater
          ie 20080810151515 is > 20080810000000

          so use >= if you want all dates and times from 11th onwards
          • 2. Re: Comparing date and string with SQL where clause
            Frank Kulash
            Hi,

            The following strings are in ascending order:

            '2008/08/09-23:59:59:999'
            '2008/08/10'
            '2008/08/10-00:00:00:000'
            '2008/08/10-00:00:00:001'
            '2008/08/10-00:00:01:000'
            '2008/08/10-23:59:59:999'

            Notice that the short string is sorted before all the others that start with the same sub-string.

            If you want to chose the strings that represent dates after August 10, 2008, you have to specify the last point in time on that date:
            where Transferreddate > '2008/08/10-23:59:59:999' -- or whatever
            The equivalent expression "on or after August 11" is harder to say than "after August 10", but easier to code:
            where Transferreddate >= '2008/08/11'
            • 3. Re: Comparing date and string with SQL where clause
              655078
              Thanks for the reply.

              Ok I got it. Since 2008/08/10 is actually 2008/08/10-00:00:00:000, I am getting correct results for "less than" queries and wrong results for "greater than" queries.

              The problem is my query includes both "less than" and "greater than". So if I append 23:59:59:999 to the date, I will get correct results for "greater than" queries but wrong results for "less than" queries.

              select * from table where Transferreddate > date1 and Transferreddate < date2.

              What can I do in this situation ?
              • 4. Re: Comparing date and string with SQL where clause
                Frank Kulash
                Hi,
                ... 2008/08/10 is actually 2008/08/10-00:00:00:000 ...
                More accurately: the table contains no 10-character strings like '2008/08/10', only 23-character strings like '2008/08/10-00:00:00:000'. You can compare strings of different lengths in a WHERE-clause, just remember the rules.

                I'm not sure I understand your problem. Even so, here are three suggestions:

                (1) If appending '-23:59:59:999' to a string causes less-than queries not to work, then don't append it in less-than queries.

                (2) If less-than queries work, but greater-than queries don't work, then don't do greater-than queries. Do greater-than-or-equal-to queries instead.

                (3) If you want to ignore the hours-minutes-seconds of Transferreddate in certain places, then don't use the full column: use only the first 10 characters. In Oracle SQL, you might say:
                where SUBSTR (Transferreddate, 1, 10) > date1 
                I understand you're not using Oracle SQL for your queries, but whatever you are using probably has an equivalent to get the first 10 characters from a longer string..
                • 5. Re: Comparing date and string with SQL where clause
                  655078
                  Alright. I think appending 23:59:59:999 to "greater than" query (date1) and keeping "less than" query as it is should work.

                  select * from table where Transferreddate > date1-23:59:59:999 and Transferreddate < date2

                  Please let me know if someone has other views ...

                  Message was edited by:
                  user652075
                  • 6. Re: Comparing date and string with SQL where clause
                    655078
                    Could someone please explain how strings are compared in Oracle. Are the strings compared character by character through ASCII values.

                    How will be '2008/08/10-00:00:00:000' and '2008/08/10' compared in Oracle?
                    • 7. Re: Comparing date and string with SQL where clause
                      damorgan
                      SELECT dump('2008/08/10-00:00:00:000'), dump('2008/08/10')
                      FROM dual;
                      • 8. Re: Comparing date and string with SQL where clause
                        Sven W.
                        Could someone please explain how strings are compared
                        in Oracle. Are the strings compared character by character through ASCII values.
                        Yes

                        >
                        How will be '2008/08/10-00:00:00:000' and '2008/08/10' compared in Oracle?
                        The answer was already given in this thread multiple times.
                        • 9. Re: Comparing date and string with SQL where clause
                          Frank Kulash
                          Hi,
                          Could someone please explain how strings are compared
                          in Oracle. Are the strings compared character by
                          character through ASCII values.
                          Basically, yes. The NLS_LANGUAGE and NLS_SORT parameters determine the sort order, which is not necessarily the same as ASCII.
                          I don't know of any language in which the numerals do not sort in ASCII order, which is all that matters in this problem.
                          • 10. Re: Comparing date and string with SQL where clause
                            655078
                            Thanks all for your help.

                            This forum is really good.