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 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
