July 13, 2009 at 1:03 pm
The following code block worked in an update statement.
Update mytable
Set mycomputedcol
Case
When End_DATE is null Then datediff(day, Convert(datetime, cast(Start_Date as nvarchar(8)), 112), getdate())
Else datediff(day, Convert(datetime, cast(Start_Date as nvarchar(8)), 112), Convert(datetime, cast(End_DATE as nvarchar(8)), 112))
END
I tried to add the following as the formula for computed column but it kept complaining about not able to validate the formula. Any ideas?
(Case
When End_DATE is null Then datediff(day, Convert(datetime, cast(Start_Date as nvarchar(8)), 112), getdate())
Else datediff(day, Convert(datetime, cast(Start_Date as nvarchar(8)), 112), Convert(datetime, cast(End_DATE as nvarchar(8)), 112))
END
)
July 13, 2009 at 1:22 pm
Simplify it to:
datediff(day, start_date, isnull(end_date, getdate())))
I just tested:
create table #T (
ID int identity primary key,
Start_Date datetime,
End_Date datetime,
Duration as datediff(day, start_date, isnull(end_date, getdate())));
--
insert into #T (Start_Date, End_Date)
select '1/1/2009', null union all
select '1/1/2009', '1/2/2009';
--
select *
from #T;
Worked just fine.
You don't need to do all that casting and converting inside DateDiff. Just adds to the overhead, doesn't actually help anything.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 13, 2009 at 2:17 pm
Thanks for your reply. isnull() is much bette than my case statement. Should have used that in the first place : ) (The developer used integer rather than date time to store the date so I had to do all those conversions first. )
However, it didn't fix the problem with Making the column a computed column.
The error message is trying to say there's a problem with the formula which is misleading. However, I was able to create a new table with the new definition. I guess the problem is I am trying to alter an existing column in an exisitng table with with computed data.
Any reasons why SQL would disallow that?
July 13, 2009 at 2:27 pm
What's the actual script you're trying to run?
Is it an Alter Table statement? If so, please provide the table definition (create script) and the Alter Table script. Then I can see what you're looking at and probably answer more effectively.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply