开发者

Update columns when value is numeric in tsql

开发者 https://www.devze.com 2023-01-01 15:31 出处:网络
I want to normalize date fields from an old badly designed db dump. I now need to update every row, where the datefield only contains the year.

I want to normalize date fields from an old badly designed db dump. I now need to update every row, where the datefield only contains the year.

update table set date = '01.01.' + date
where date like '____' and is开发者_如何学Gonumeric(date) = 1 and date >= 1950

But this will not work, because sql does not do short circuit evaluation of boolean expressions. Thus I get an error "error converting nvarchar '01.07.1989' to int"

Is there a way to work around this? The column also contains strings with a length of 4, which are not numbers (????, 5/96, 70/8, etc.) the table only has 60000 rows


You could always do a subquery

update table set date = '01.01.' + date
where id in(

   select id from table
   where date like '____' and isnumeric(date)

)
and convert(int, date) >= 1950


How about just enclosing 1950 in single quotes?

I ran a quick test:

DECLARE @Table TABLE (Date NVARCHAR(20))
INSERT @Table 
SELECT '2010' 
UNION ALL 
SELECT '2009' 
UNION ALL 
SELECT '01.07.1989' 
UNION ALL 
SELECT '1951'
UNION ALL
SELECT '1940'
UNION ALL
SELECT 'Some Text'

UPDATE @Table
SET Date = '01.01.' + date
WHERE date LIKE '____' AND ISNUMERIC(date) = 1 AND date>='1950'

SELECT * FROM @Table


Maybe add a new field to store actual datetime Value:

alter table [Table] add [RealDate] datetime;
update [Table] set [RealDate] = cast([Date] as datetime) where isdate([Date]) = 1
0

精彩评论

暂无评论...
验证码 换一张
取 消