Set question - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Set question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-03 : 13:06:03

I wish to use:

DECLARE @myDateField varchar(20)
SET @myDateField = 'Added_By_Date'

UPDATE dbo.format_bulletin_information

SET @myDateField = NULL
WHERE @myDateField = '0'

SET @myDateField = '200' + SUBSTRING(@myDateField,3,LEN(@myDateField))
WHERE LEFT(@myDateField,2) = '10'

to null the date field if it contains a zero and manipulate it if it doesn't.

The script works find without the:

SET @myDateField = NULL
WHERE @myDateField = '0'

Where am I going wrong please?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-03 : 13:09:08

[code]
UPDATE dbo.format_bulletin_information
SET yourColumn = case when @myDateField = '0' then null
when LEFT(@myDateField, 2) = '10' then '200' + SUBSTRING(@myDateField,3,LEN(@myDateField))
end
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-03 : 13:37:58

Many thanks.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-04 : 08:32:01

Hmm .. I'm finding that the script:

UPDATE dbo.format_bulletin_information
SET yourColumn = case when @myDateField = '0' then null
when LEFT(@myDateField, 2) = '10' then '200' + SUBSTRING(@myDateField,3,LEN(@myDateField))
end

is not changing the value to null when the column row is zero, but the date string manipulation works fine. I can't figure out why.

Any ideas guys?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 09:27:38

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95764

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources