[thelist] bad sql data

Joshua Olson joshua at waetech.com
Mon Nov 28 08:37:11 CST 2005


> From: Ken Schaefer
> Sent: Sunday, November 27, 2005 10:45 PM

> What you can do is parse the date/time you extract, convert 
> it to ISO format:
> yyyymmdd hh:mm:ss and then insert it into the new database. 
> If you are doing a straight INSERT INTO...SELECT then use 
> CONVERT() with 112 as the style (112 is ISO-style).

Ken,

This entire post was excellent.  Great information about dates and SQL
Server as well as Internationalization of code.

<tip type="SQL" author="Joshua Olson">
  In SQL Server, you can use Coalesce to "cascade" values 
  from a secondary table if the primary table has a NULL value.  
  This is useful in a business rules dictate that a field may have a 
  "default" value if no override value has been assigned.  

  Example: An item may have a defined shipping cost, but if no 
  cost is defined for the item, then the shipping cost should 
  default to $5.  If the default value can change, you may wish 
  to store the value in another table instead of populating the 
  values with $5.  (if you arbitrarily populated the values with 
  $5, then you wouldn't be able to differentiate between items 
  that are being shipping at $5 on purpose or because the was 
  no shipping cost!)

  The query may look like this:

  SELECT name
  , Coalesce
    (
        shipping_cost
      , dbo.getConfig('default_ship')
    ) AS shipping_cost
  FROM item
  WHERE id = 1

  NOTE: We are assuming here that the custom function
  dbo.getConfig is responsible to returning the value of
  'default_ship' from the configuration table.
</tip>

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/





More information about the thelist mailing list