convert days to years, months, days result? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

convert days to years, months, days result?

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2012-01-13 : 14:28:10

Hello well I'm having issues with diffdate. I would like it to display the years, months and days. This is my query:

SELECT DATEDIFF(d, items.ReceiptDate, items.LAST_SOLD) AS Months
FROM items INNER JOIN
RTotals ON items.itemsID = RTotals.RunningTotals.itemsID
WHERE (items.VID = '1176') AND (items.bqty > 0)

that displays example 279 and 113. I would like it to show a result as 2 years, 1 month, 2 days. How can I get it to converts a day result into years, months, days?

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-13 : 16:04:53

There has to be an easier way to do this, but whatever you do, test it with leap years, dates in the same year, etc. In any case, here is my admittedly convoluted way of doing it:

;WITH cte AS
(
	SELECT 
		items.ReceiptDate,
		items.LAST_SOLD,
		
		(
			CAST(CONVERT(CHAR(8),items.LAST_SOLD,112) AS INT)
			-
			CAST(CONVERT(CHAR(8),items.ReceiptDate,112) AS INT)
		)/10000 AS [Years],
		
		(DATEDIFF(MONTH,items.ReceiptDate,items.LAST_SOLD)+12)%12 
			- CASE WHEN DAY(items.ReceiptDate)>DAY(items.LAST_SOLD) THEN 1 ELSE 0 END
		 AS [Months]
	FROM items INNER JOIN
	RTotals ON items.itemsID = RTotals.RunningTotals.itemsID
	WHERE (items.VID = '1176') AND (items.bqty > 0)
)
SELECT
	*,
	DATEDIFF(day,DATEADD(month,[Months],DATEADD(YEAR,[Years],ReceiptDate)),LAST_SOLD) AS [Days]
FROM
	cte;

My logic is based on this simple example:

DECLARE @d1 DATETIME, @d2 DATETIME;

SET @d1 = '20070228';
SET @d2 = '20080301';

DECLARE @years INT, @months INT, @days INT;

SET @years = (CAST(CONVERT(CHAR(8),@d2,112) AS INT)
   -CAST(CONVERT(CHAR(8),@d1,112) AS INT))/10000;
SET @months = (DATEDIFF(MONTH,@d1,@d2)+12)%12 - CASE WHEN DAY(@d1)>DAY(@d2) THEN 1 ELSE 0 end;
SET @days = DATEDIFF(day,DATEADD(month,@months,DATEADD(YEAR,@years,@d1)),@d2);


SELECT @years,@months, @days;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 00:14:58

[code]
SELECT DATEDIFF(d, items.ReceiptDate, items.LAST_SOLD)/365 AS Years,
(DATEDIFF(d, items.ReceiptDate, items.LAST_SOLD)%365)/30 AS Months,
(DATEDIFF(d, items.ReceiptDate, items.LAST_SOLD)%365)%30 AS Days
FROM items INNER JOIN
RTotals ON items.itemsID = RTotals.RunningTotals.itemsID
WHERE (items.VID = '1176') AND (items.bqty > 0)
[/code]

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

taunt
Posting Yak Master

128 Posts

Posted - 2012-01-16 : 13:00:31

This works perfectly. Odd thing is sql & coldfusion have a little bit of difference in the coding and get differenece results. Coldfusion I use DATEDIFF("d", qsearch.ReceiptDate, qsearch.LAST_SOLD)/365 as Years and get back 2.81369863014 Years, 7 Months, 27 Days for one result, and in sql it's just 2 Years 9 Months 27 Days. But this was exactly what I was looking for. Thanks

quote:
Originally posted by visakh16

SELECT DATEDIFF(d, items.ReceiptDate, items.LAST_SOLD)/365 AS Years,
(DATEDIFF(d, items.ReceiptDate, items.LAST_SOLD)%365)/30 AS Months,
(DATEDIFF(d, items.ReceiptDate, items.LAST_SOLD)%365)%30 AS Days
FROM items INNER JOIN
RTotals ON items.itemsID = RTotals.RunningTotals.itemsID
WHERE (items.VID = '1176') AND (items.bqty > 0)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 13:32:37

welcome

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-16 : 13:53:19

Please correct me if I'm wrong, but that solution is only an aproximation, right?

taunt
Posting Yak Master

128 Posts

Posted - 2012-01-16 : 17:01:43

Yep it's not to a tee cause it can't be with leap year and different days in a month.

quote:
Originally posted by Lamprey

Please correct me if I'm wrong, but that solution is only an aproximation, right?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources