Discrepancies in the way ColdFusion validates dates.
There seems to be discrepancies in the various date validations used throughout ColdFusion, or at least I've found one case where it inconsistently considers a value a valid date. It's probably easier to demo what I'm talking about than to try to explain it. Here's an example:
Let's say you have the following code where you create a date and format it with DateFormat(), then run a check to see if it's a valid date.
<cfoutput>#isDate(myDate)#</cfoutput>
This would result in IsDate() returning "Yes" because it perceives this to be a valid date, which is what you would expect.
Now, what happens if you use the exact same code and just remove the dashes from the DateFormat()?
<cfoutput>#isDate(myDate)#</cfoutput>
Given today's date, that would translate to '02082010'. This time IsDate() will return "No", because there is no distinguishable formatting between the values and it's really just a string of numbers now with no way for CF to tell what part of it is the day or month or year.
However, if you take that same variable and pass it to a cfc as an argument that has type="date", ColdFusion will accept the value as a valid date even though it failed IsDate(). Take it a step further, and use that same value in a cfqueryparam tag that has cfsqltype="cf_sql_date", ColdFusion still accepts the value as a date and passes it through to the query.
Now, here's where the fun begins. When ColdFusion uses that value in a query, cfqueryparam translates our date string of '02082010' to {ts '7600-05-06 00:00:00'}, which is not even remotely accurate. If we go back and use our original date with the formatting of 'mm-dd-yyyy', everything works as expected and is passed through correctly to the query.
Evidently, the date validations in cfargument and cfqueryparam are not doing the same thing the date validation in IsDate() does. Personally, I would have expected them to use the same logic as IsDate() under the hood, in that if it didn't pass IsDate() then it wasn't a date and would throw an error if you tried to use it in cfargument or cfqueryparam.
I haven't had a chance to test this on Railo yet, but I plan on doing that tonight.
<cfoutput>#isDate(myDate)# - #DateFormat(myDate,'short')#</cfoutput>), it will output "NO - 9/22/27 ".
ASandstrom on livedocs for version 6 says:Passing numeric data to the ColdFusion IsDate and ParseDateTime functions can yield unexpected results. For example, IsDate may return TRUE for numeric data that are not dates.
"To work around this behavior, it is always advisable to only pass string data in a supported format to the IsDate and ParseDateTime functions, or, generate date-time data using the Now or CreateDate functions."
mysql and other DBs will convert a string to a date if it makes sense. http://dev.mysql.com/doc/refman/5.1/en/datetime.ht...
It looks like the leading 0 may be throwing off the isDate function where cfqueryparam and cfargument ignore the leading 0.
Ignoring the leading zero will yield a valid SERIAL date, 2082010 days from 0/1/1900.
2082010 / 365 + 1900 = 7604 which is pretty close to the year you got. there's more to that formula but that's the gist of it anyway. Here's some information about working with serial dates. http://www.cpearson.com/excel/datetime.htm
When I originally wrote this post, I was thinking that IsDate() was right and cfqueryparam and cfargument were allowing non-dates through and mucking them up. But after thinking about what you said and looking at the IsNumericDate() function I'm beginning to think that IsDate() is slacking and not covering all of it's bases to determine if the given value is a date.
To me, IsDate() should be the Mac Daddy of all the date functions. It should check to see "is this a date in any way, shape, form, or fashion?", not "is this a date that you've already formatted like a date?". Basically, if it's not a date in IsDate(), it shouldn't be considered a date anywhere else.