March 19, 2008 at 3:56 pm
Hi guys I have got two columns and I need to create a new third column which is based on some if else statement.
Here is the sample data:
StartDate1 StartDate2 NewDate
12/4/2000 11/5/2000 12/4/2000
14/3/2006 20/3/2006 14/3/2006
1/1/2002 1/1/2003 1/1/2003
It works like that if the difference between startdate1 and startdate2 is <=30, use use startdate1 in the newdate column or else use the startdate2.
How do I do this in a query??
Thanks
March 19, 2008 at 4:16 pm
You mean something like this:
select
StartDate1,
StartDate2,
case when datediff(dd,StartDate1, StartDate2) <= 30
then StartDate1
else StartDate2
end as NewDate
from
dbo.MyTable
😎
March 19, 2008 at 5:15 pm
Hi
It gives me the syntax error like this
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CASE'.
What to do??
March 19, 2008 at 7:13 pm
Nuts,
I did not see any issues. here is the table and sample data, since your question lacked it.
CREATE TABLE mytable
(
startdate1 DATETIME,
startdate2 DATETIME
)
INSERT INTO mytable
VALUES ( GETDATE(), GETDATE() - 1 )
INSERT INTO mytable
VALUES ( GETDATE(), GETDATE() + 1 )
INSERT INTO mytable
VALUES ( GETDATE(), GETDATE() - 32 )
INSERT INTO mytable
VALUES ( GETDATE(), GETDATE() + 32 )
select StartDate1,
StartDate2,
case when datediff(dd, StartDate1, StartDate2) <= 30 then StartDate1
else StartDate2
end as NewDate
from dbo.MyTable
If you still get the error, give us some info so we can reproduce it - this worked for me.
-- Cory
March 27, 2008 at 2:38 pm
Works well
Thanks:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply