This content has been marked as final. Show 10 replies
So the problem is that you are getting 2008/08/10 and you want all dates after it.
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
The following strings are in ascending order:
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 whateverThe equivalent expression "on or after August 11" is harder to say than "after August 10", but easier to code:
where Transferreddate >= '2008/08/11'
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 ?
... 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) > date1I 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..
Could someone please explain how strings are comparedBasically, yes. The NLS_LANGUAGE and NLS_SORT parameters determine the sort order, which is not necessarily the same as ASCII.
in Oracle. Are the strings compared character by
character through ASCII values.
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.