Else 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)

Else question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-09-25 : 10:16:00

What am I doing wrong with the else portion of this script please?

use Pardata

update transactions_additional

set Effective_Date_1 =
case
when Effective_Date is null
then null
when Effective_Date LIKE '10%'
then '20' + substring(Effective_Date,2,len(Effective_Date)
else
'19' + Effective_Date
end

My table had weird dates (varchar column) like 1060101 for 20060101
or 890101 for 19890101 (not so weird) and I need to convert this column into YYYYMMDD format.

Any pointers would be much appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:35:34

Keep track of your ending paranthesises

UPDATE	transactions_additional
SET	Effective_Date_1 =	CASE
					WHEN Effective_Date IS NULL THEN NULL
					WHEN Effective_Date LIKE '10%' then '20' + SUBSTRING(Effective_Date, 2, LEN(Effective_Date) - 1)
					ELSE '19' + Effective_Date
				END

E 12°55'05.63"
N 56°04'39.26"

CVDpr
Starting Member

41 Posts

Posted - 2008-09-25 : 16:03:09

quote:

Originally posted by Peso

Keep track of your ending paranthesises

UPDATE	transactions_additional
SET	Effective_Date_1 =	CASE
					WHEN Effective_Date IS NULL THEN NULL
					WHEN Effective_Date LIKE '10%' then '20' + SUBSTRING(Effective_Date, 2, LEN(Effective_Date) - 1)
					ELSE '19' + Effective_Date
				END

E 12°55'05.63"
N 56°04'39.26"

what the -1 do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 01:51:32

Really no need to put -1 at the end. SQL Server will know when string is ended.

E 12°55'05.63"
N 56°04'39.26"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-09-26 : 03:18:49

Many thanks Peso - much appreciated.