Oops! I find using a 3 char month avoids the date format problem quite well, which is common in oracle land. 2-feb-08 is clearer and will be never be confused
On 4/8/08, Zac Spitzer <[EMAIL PROTECTED]> wrote: > It's doof > > On 4/8/08, barry.b <[EMAIL PROTECTED]> wrote: > > > > lesson 2: setting NULL values > > > ---------------------------------------------------------------------------------- > > > > consider this INSERT statement within a CFC (just one field out of > > many). the db is SQLServer and the field a datetime**: > > > > CASE > > WHEN <cfqueryparam cfsqltype="CF_SQL_VARCHAR" > > value="#FORM.IT_Request_Date#"> <> '' > > THEN > > CONVERT(datetime,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" > > value="#FORM.IT_Request_Date#">,103) > > ELSE > > NULL > > END, > > > > this says: > > > > if the value is NOT '' (empty string) then convert the string to a > > datetime and insert else insert NULL > > > > regarding inserting/updating with NULL: > > > > there is an attribute within CFQUERYPARAM called "null" that, when is > > true, inserts a NULL into that field (regardless of the value in the > > "value" attribute) > > > > How do you automatically know when your data needs this NULL for the > > insert? check the length of the data for length = 0. > > > > <cfqueryparam > > cfsqltype="cf_sql_timestamp" > > value="#FORM.IT_Request_Date#" > > null="#YesNoFormat(not(len(FORM.IT_Request_Date)))#" > > /> > > > > what's all this mess? ... > > #YesNoFormat(not(len(FORM.IT_Request_Date)))# > > > > > > the YesNoFormat() (which is optional) will convert any 1/0 to true/ > > false (or Yes/No) > > > > len(FORM.IT_Request_Date) will return 0 if it's an empty string. But > > for us, "0" means "true - give us a NULL instead". That's what the > > NOT() does -reverses the 0 to 1 and therefore "length=0" to "TRUE" > > > > more info from someone else to confirm this: > > > http://www.chapter31.com/2007/02/04/cfqueryparam-and-conditional-handling-of-nulls/ > > > > ==================================================== > > > > lesson 3: converting string dates to real database dates. > > > ---------------------------------------------------------------------------------- > > > > Dates coming from forms are strings - not true datetime objects (at > > least not like the datetime objects expected by databases like > > SQLServer). > > > > Most of the time ColdFusion can happily convert it but sometimes an > > ommision either throws an error, inserts an impossible date or simply > > confuses AU format (dd/mm/yyyy) with US (mm/dd/yyyy). > > > > so... > > > > instead of converting strings with SQL to the 103 locale (Australia), > > let some ColdFusion functions and CFQUERYPARAM help you > > > > from this > > > > CONVERT(datetime,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" > > value="#FORM.IT_Request_Date#">,103) > > > > to this: > > > > <cfqueryparam > > cfsqltype="cf_sql_timestamp" > > value="#LSParseDateTime(FORM.IT_Request_Date)#" > > null="#YesNoFormat(not(len(FORM.IT_Request_Date)))#" /> > > > > many people have had problems with cf_sql_timestamp (SQLServer- > > datetime-length:8) but the trick is to use LSParseDateTime() which > > will make a reasonable attempt to turn anything that looks like a date > > string into a proper date object. No mention of time means a default > > to midnight ("00.00.000") after LSParseDateTime() is used. > > > > in this case "cf_sql_timestamp" works quite happily with SQLServer- > > datetime-length:8 - provided that it recieves a datetime object (eg > > via LSParseDateTime() ), not a string representing one. No need for > > createODBCdate or splitting a dd/mm/yyyy on the "/" and reassembling. > > > > the added bonus is that the "LS" locale-specific series of functions > > will use the CF server settings to know that 01/02/2008 means the > > first of Feb 2008 (dd/mm/yyyy) and not 2nd of Jan (mm/dd/yyyy - US > > format) > > > > see: > > http://livedocs.adobe.com/coldfusion/6.1/htmldocs/functa36.htm > > > > ========================================================= > > > > these tips - and many more - are available _free_ from your local > > ColdFusion user group. Open to all, no charge and catering > > provided.There's probably a CFUG near you. > > > > The Qld CF user group has a meeting tonight. Why not come along? > > more info here: http://qld.cfug.org.au/ > > > > ========================================================= > > > > > > ** ignore FORM scope within a CFC - that's worth mentioning another > > time > > > > > > > > > > > > -- > Sent from Gmail for mobile | mobile.google.com > > Zac Spitzer - > http://zacster.blogspot.com (My Blog) > +61 405 847 168 > -- Sent from Gmail for mobile | mobile.google.com Zac Spitzer - http://zacster.blogspot.com (My Blog) +61 405 847 168 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
