Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maevr
Posting Yak Master
169 Posts |
 Posted - 2010-12-20 : 01:41:48
|
| I have splitted an xml to different columns to a tmp_table storing only varchar columns, the next step converts the data into different datatypes in a new ba_table. I need help update a datetime column from a varchar column where to users has stored all types of valid and invalid formats. My insert fails everytime because the format of the varchar is invalid and convert cannot continue.How can I update this column if the values are correct and all the others to null?Can I use a try/catch on this column alone inside the insert script to the ba_tablePreferred format: yyyy-mm-ddMy reduced insert:insert into ba_table(id, col1, col2)(select id, convert(datetime, col1) as col1, convert(int, col2) as col2 from tmp_table) |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-20 : 03:12:10
|
| [code]SET DATEFORMAT YMDinsert into ba_table(id, col1, col2)(select id, CASE WHEN IsDate(col1) = 1 THEN convert(datetime, col1) ELSE NULL END as col1, convert(int, col2) as col2 from tmp_table)[/code]may help |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
update datetime column from varchar.