Question on Diff between two date fields in CASE

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)

Question on Diff between two date fields in CASE

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-17 : 10:48:51

Hi,

I have a Date field say InquiryDate. I have another date field say Maturity.

I have the below case statement. But I would like to find the difference between the two date fields and then assign the CASE statement values. I have not written the SYNTAX for the date difference. Also in my Query, the tables that these fields come from are joined.

Maturity = CASE
WHEN DateDiff(Maturity and InquiryDate)<= 1 THEN '<= 1 Year'
WHEN DateDiff(Maturity and InquiryDate)<= >1 AND iss.Maturity <= 3 THEN '<= 3 Years'
WHEN DateDiff(Maturity and InquiryDate)<= > 3 AND iss.Maturity<= 5 THEN '<= 5 Years'
WHEN DateDiff(Maturity and InquiryDate)<= > 5 THEN '> 5 Years'
END

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-17 : 11:16:27

I think I got it.

Datediff(yy,inq.InquiryDate,iss.Maturity)

where for example if InquiryDate, Maturity are as below I get the diff(MaturityDate) in years as 7
InquiryDate Maturity MaturityDate
2010-01-04 08:01:43.000 2017-05-02 00:00:00.000 7

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 11:38:54

it wont always work well. consider the values

InquiryDate Maturity
2010-01-04 08:01:43.000 2010-01-22 00:00:00.000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 11:40:51

better to do it as Datediff(mm,inq.InquiryDate,iss.Maturity)/12

or Datediff(mm,inq.InquiryDate,iss.Maturity)/12.0 if you need decimal also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources