DateDiff returning 0 - 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)

DateDiff returning 0

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-26 : 09:50:45

Hi,

I have two datime fields. InquiryDate and MaturityDate. I use the below convert funtion to get the values as

MaturityDate InquiryDate
2010 2010
2019 2010
2025 2010

convert(int,datepart(yy,iss.Maturity)) AS MaturityDate,
convert(int,datepart(yy,inq.InquiryDate)) AS InquiryDate

iss is the Issue table and inq is the Inquiry table that use in my JOINS.

The below conversion gives me 0. Not sure why.

ABS(Datediff(yy,dlr.MaturityDate,dlr.InquiryDate))

But when I try the below formula to test I get the correct difference.

DECLARE @StartDate datetime,
@EndDate datetime
SET @StartDate = '2019'
SET @EndDate = '2010'
SELECT ABS(Datediff(yy,@StartDate,@EndDate))

Value is 9

What am I doing wrong in my Datediff formula?

Thanks

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-26 : 09:52:18

When I try declaring the fields as int I get 0

DECLARE @StartDate int,
@EndDate int
SET @StartDate = 2019
SET @EndDate = 2010
SELECT ABS(Datediff(yy,@StartDate,@EndDate))

Do I need to use convert(datetime)?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 09:58:05

Run this code, and it will give you a clue. Notice the 2 variable are declared as dates.


DECLARE @StartDate DATETIME,
@EndDate DATETIME
SET @StartDate = 2019
SET @EndDate = 2010
SELECT @StartDate, @EndDate

There are 10 types of people in the world, those that understand binary, and those that don't.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 09:58:49

That should be all (no need to convert):
select
ABS(year(iss.Maturity) - year(inq.InquiryDate)) as Diff_in_Years
from ...

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-26 : 10:06:21

Try this

DECLARE @StartDate int,
@EndDate int
SET @StartDate = 2019
SET @EndDate = 2010
SELECT Datediff(year,'01-Jan-'+ cast(@StartDate as varchar(10)),'01-Jan-'+ cast(@EndDate as varchar(10)))

Vaibhav T

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 10:17:37

It looks like the problem is that you're storing the year in an integer field, and then passing it to the DATEDIFF function. This causes them to be implicitly converted into datetimes.

When SQL Server converts an int to a datetime, it uses the int to represent the number of days after 1900-01-01. 1 is converted to '1900-01-02 00:00:00.000' and 2 to '1900-01-03 00:00:00.000'.

In the expression DATEDIFF(yy, 1, 2), both 1 and 2 are converted into dates as per the above. These dates both have the same year, and as such, the DATEDIFF function returns 0.

If you have 2 integers that represent years, and you want to know the difference between them, then just treat them like integers and use the - operator. if dlr.MaturityDate and dlr.InquiryDate are both integer fields, representing a year, then:

ABS(dlr.MaturityDate - dlr.InquiryDate)

There are 10 types of people in the world, those that understand binary, and those that don't.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-26 : 10:42:43

Wow! Yup when I retrieved the rows for the int to datetime conversion I got all values of 1900. Thanks you so much for the explanation, makes a lot of sense. Date conversions can be made confusing by us if we do not understand the way it works thoroughly :-)

I have another question related to the Date Difference.

If I pass a paramater @Maturity varchar(100) to the stored proc. This parameter is a drop down in a GUI tool vial some look up procs.

The values exec by the lookup procs are

Key Values
All Maturity All Maturity
<= 1 Year <= 1 Year
>1 & <= 3 Years >1 & <= 3 Years
>3 & <= 5 Years >3 & <= 5 Years
> 5 Years > 5 Years

The above are the values displayed in the drop down box.

I have a Low and Hig range that I declare in my proc as below

--For Where Clause for Maturity High and Low
declare @LowMaturity int,
@HighMaturity int

SET @LowMaturity = case @Maturity when '<=1 Year' then 0
when 'All Maturity' then 0
when '>1 Year & <=3 Years' then 1
when '>3 Years & <=5 Years' then 3
else 5
end

SET @HighMaturity = case @Maturity when '<=1 Year' then 0.999
when 'All Maturity' then 99999999
when '>1 Year & <=3 Years' then 3
when '>3 Years & <=5 Years' then 5
else 99999999
end

I will use
SELECT
Maturity = @Maturity

WHERE
ABS(year(iss.Maturity) - year(inq.InquiryDate)) between @LowMaturity and @HighMaturity

GROUP BY
ABS(year(iss.Maturity) - year(inq.InquiryDate)) between @LowMaturity and @HighMaturity

I can send the whole code as I am doing JOINS and inserts into temp tables if needed.

I would like to see the Maturtiy values as

<= 1 Year case when I pass <= 1 Year for @Maturity. This works fine.

But the problem is when I pass 'All Maturity' the value returned is 'All Maturity'

I want my logic to look for the Diff which I got by your formula:

ABS(year(iss.Maturity) - year(inq.InquiryDate)) and then when this value is between the @LowMaturity and @HighMaturity, I want to see all ranges.

So if it is between 0 and say 9999999 I want to see all the below ranges for Maturity.

is there a better way to do this using CASE? I have to pass @Maturity as a paramater.

<= 1 Year
>1 & <= 3 Years
>3 & <= 5 Years
> 5 Years

Thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 11:05:47

Firstly, I'd have a table that looks like this (csv)

Key, Values, LowMaturity, HighMaturity, Defunct
1, "All Maturity", 0, 999999, 0
2, "<= 1 Year", 0, 1, 0
3, ">1 & <= 3 Years", 1, 3, 0
4, ">3 & <= 5 Years", 3, 5, 0
5, "> 5 Years", 5, 999999, 0

I'd populate the dropdown from this table, using:


SELECT Key, Values
FROM tbl
WHERE Defunct = 0

Once a value has been selected, I'd pass the key back to the stored proc, and then you could populate the Low/HighMaturity values using this query:


SELECT @LowMaturity = LowMaturity, @HighMaturity = HighMaturity
FROM tbl 
WHERE @Key = Key

By doing this, it will be easy to add different maturity values at a later date by adding records to the table. The Defunct flag can be used to disable them, so the cannot be added to new records, but will still work with any queries against existing records.

There are 10 types of people in the world, those that understand binary, and those that don't.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-26 : 11:43:29

The key table that populates the drop down with varied selection values cannot be modified as this is a standard used. i cannot add another column to the key table.

using the existing key table which has the columns as below?

Key, Values, Persist

This should be fairly easy but I am unable to get the values for All Maturity.

Thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 12:14:40

quote:
Originally posted by sqlnovice123

But the problem is when I pass 'All Maturity' the value returned is 'All Maturity'

I'm not sure what you mean by this. Can you go into more details please?

There are 10 types of people in the world, those that understand binary, and those that don't.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-26 : 12:20:54

When the parameter value 'All Maturity' is passed to the proc then I want the Maturity column to be populated with the values based on the Diff column , the formula which you provided:

So when Diff_in_Years value is 18, Maturity column should show the value as > 5 Years.
When Diff_in_Years value is 1, Maturity column should show the value as <= 1 Year
When Diff_in_Years value is 4, Maturity column should show the value as > 3 Years & <= 5 Years.

Currently, the Diff_in_Years column shows the correct values but the Maturity column shows All Maturity for all rows which is not correct.

ABS(year(iss.Maturity) - year(inq.InquiryDate)) as Diff_in_Years

DECLARE
@StartDate datetime,
@EndDate datetime,
@ClientType varchar(50),
@Product varchar(50),
@ListID varchar(50),
@IsBuy varchar(20),
@SizeBucket varchar(100),
@Maturity varchar(100)

SET @StartDate = '20100101'
SET @EndDate = '20100201'
SET @ClientType = '154246'
SET @Product = '154247'
SET @ListID = 'All Inquiries'
SET @IsBuy = 'Bid/Offer'
SET @SizeBucket = 'All Sizes'
SET @Maturity = '>1 Year & <=3 Years'

Please let me know if my expanation is clear.

Thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 12:44:37

From your earlier post:


SELECT
Maturity = @Maturity

I think that's the problem. This says to return a column called Maturity, and populate this column with the value of @Maturity in every row. When you SET @Maturity = 'All Maturity', it returns all rows, but the Maturity column in each of those rows is being populated by the @Maturity variable, which does not change, and you have already set to equal 'All Maturity', so that's what is returned in each row.

Instead, you could either join onto the key table, and select the Values Column. This would be difficult, as there's no additional parameters in that table (like the ones I suggested earlier). Or you could use a case statement, based on the result of the Diff, with hard coded values for The maturity period. Something like:


SELECT Diff_in_Years, CASE 
		WHEN Diff_in_Years <= 1 THEN '<= 1 Year'
		WHEN Diff_in_Years <= 3 THEN '> 1 Year <= 3 Years'
		WHEN Diff_in_Years <= 5 THEN '> 3 Years <= 5 Years'
		ELSE '> 5 Years' END AS Maturity, (Other fields as required)
FROM (	SELECT	ABS(year(iss.Maturity) - year(inq.InquiryDate)) as Diff_in_Years, (Other fields as required)
		FROM ...
		INNER JOIN ...
)

There are 10 types of people in the world, those that understand binary, and those that don't.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-26 : 13:19:22

This works fine in the final select.

If I had to use the High and Low Range, I am curious as to whether Ic an use the CASE statement in the select?

Where I have

SELECT
Maturity = @Maturity

Can I use instead of Diff, the ABS function ? I get an error when I try it. The reason I am trying this is to see what value I get for Maturity in my First insert into the temp table

SELECT
Maturity = CASE
WHEN Diff <= 1 THEN '<= 1 Year'
WHEN Diff > 1 AND Diff <= 3 THEN '> 1 Year <= 3 Years'
WHEN Diff >3 AND Diff <= 5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years' END

SELECT Maturity =
CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <= 1 THEN '<= 1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '> 1 Year <= 3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '> 3 Years <= 5 Years'
ELSE '> 5 Years'

Thanks for your help

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 13:22:19

Yes, you can do it that way. The only reason I used a subquery was to avoid the evaluation of ABS(year(iss.Maturity) - year(inq.InquiryDate)) multiple times. The DB Engine may be smart enough to evaluate it only once anyway.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources