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..
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:
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?
SELECT dump('2008/08/10-00:00:00:000'), dump('2008/08/10')
Could someone please explain how strings are comparedYes
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?The answer was already given in this thread multiple times.
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.
Thanks all for your help.
This forum is really good.