How about <cfqueryparam value="#myDate#" cfsqltype="cf_sql_datetime" null="#not isDate(myDate)#" />
/Max On 3/5/07, Charlie Arehart <[EMAIL PROTECTED]> wrote: > > Mike, I'll take a shot, but first I want to admit I'm not sure I'm > appreciating all the nuances of what you've put forth. Are you saying > basically that your insert/update fails because it expects either a date or > null for this dateapproved column? It's not clear, because you never refer > to a variable being the input for the date. You show either now() or empty > string, but perhaps that was just for making a point about their being a > date or not. > > If the date is indeed a variable (let's call it mydate), could you not > then just add some CFML to test for the empty string or not: > > dateapproved = <cfif mydate is "">NULL<cfelse><cfqueryparam, > value="#mydate#" cfsqltype="cf_sql_datetime"/></cfif> > > Sorry if that's obvious and therefore not the issue. It's just that I > could just see someone concluding for some reason that they couldn't do a > CFQueryparam inside an IF, when indeed you can. (And for anyone who would > prefer the IIF over CFIF in this kind of statement, that doesn't work here > because it can't take tags, like the CFQueryParam, in its arguments, at > least as far as I know). > > /Charlie > http://www.carehart.org/blog/ > > > > ------------------------------ > *From:* cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] *On > Behalf Of *Mike Kear > *Sent:* Sunday, March 04, 2007 1:01 AM > *To:* cfaussie > *Subject:* [cfaussie] Another CFQUERYPARAM peculiarity > > > Something that's been bugging me for a while but i never got around to > finding the reason/fix for the problem. I figured out a workaround and > never got back to the fix. Here's the problem .. > > i have a date field in a table thats (for example) specced as follows: > > DateApproved (datetime) Null > > If i insert into that field, I can use this in my cfquery insert > statement no problem: > > <cfqueryparam, value="#now()#" cfsqltype="cf_sql_datetime"/> > > But if there's no value yet, (e.g if this record isnt approved yet) I get > an error. I'd have thought if the value for DateApproved (in this example) > is 'no value' then just having the following in the insert statement would > work. No? > > <cfqueryparam, value="" cfsqltype="cf_sql_datetime"/> > > The workaround I've been using isnt really satisfactory because it defeats > the purpose of <cfqueryparam. What i do is to drop the CFQUERYPARAM and use > the following in the insert: > > #createODBCDateTime(now())# or nothing if there isn't a date yet. I > suppose i could ensure there is always a date, and (to use this example) > where the recod isnt approved, set the date to 1/1/1901 or something but > that would seem to defeat the purpose too. > > Anyone see what i i'm doing wrong? > > > Cheers > Mike Kear > > > > -- Mobile: 0424 77 57 67 Home: 02 9968 3924 Skype: maxnyman MSN Messenger: [EMAIL PROTECTED] --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
<<attachment: att4e9a6.jpg>>