Last post Jan 11, 2017 01:18 PM by gkguru
Oct 12, 2016 08:08 AM|pn_nq|LINK
Oct 12, 2016 09:58 AM|pn_nq|LINK
Oct 12, 2016 10:58 AM|PatriceSc|LINK
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.
Oct 12, 2016 11:30 AM|pn_nq|LINK
Oct 12, 2016 12:00 PM|PatriceSc|LINK
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 :
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 ????)
Oct 12, 2016 12:05 PM|RichardY|LINK
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.
Jan 11, 2017 01:18 PM|gkguru|LINK
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() // => trueGK GURU