[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