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.
| Author |
Topic |
|
chinnanu143
Starting Member
13 Posts |
 Posted - 2010-11-24 : 05:49:26
|
| this is my tableprojectsprojectid deptcode FK description startdate enddate revenueEMPHAPPY ADMIN Employee Moral 2002-03-14 NULL 0.00ADT4MFIA ACCNT Mofia Audit 2003-07-03 2003-11-30 100000.00ROBOSPSE CNSLT Robotic Spouse 2002-03-14 NULL 242000.00DNLDCLNT CNSLT Download Client 2005-02-03 NULL 18150.00--Find the project ID and duration of each project--Find the project ID and duration of each project. If the project has not finished, report its execution time as of now.ok hear to solve above query we can use select statement but how to find duration when enddate is null value.we can take those projects as not finished ones.but how to get duration for them even we take enddate as presentdate what i tried isselect projectid ,(startdate-getdate()) as duration from projectsbut what we want is we have to get presentdate if enddate is null only...i hope i am clear about my question...thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-11-24 : 06:01:28
|
Takes getdate() only, if enddate is null:COALESCE(enddate,getdate())
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-24 : 06:01:47
|
| use coalesce(endate, getdate())That will return the enddate unles it's null in which case it will return th current date.If you just want the date without the time usecoalesce(endate, dateadd(dd,*datediff(dd,0,getdate()),0)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
chinnanu143
Starting Member
13 Posts |
Posted - 2010-11-24 : 09:47:50
|
| thank you |
|
|
|
|
|
|
hi