January 16, 2017 at 1:55 am
Dear Friends,
I need help to count how many years,months, days left between two dates.
the data:
acquisition_date expiration_date years months days withdrawal/delay
2012-03-22 2017-03-22 ? ? ? ?
how the syntax to count that data ?
thank you very much
January 16, 2017 at 2:13 am
Use the DATEDIFF function to get the number of years, then use DATEADD to add that to the acquisition date, then use the DATEDIFF function again to get the number of months, then use DATEADD again... I'm sure you get the picture.
John
January 16, 2017 at 2:36 am
John Mitchell-245523 - Monday, January 16, 2017 2:13 AMUse the DATEDIFF function to get the number of years, then use DATEADD to add that to the acquisition date, then use the DATEDIFF function again to get the number of months, then use DATEADD again... I'm sure you get the picture.John
Hi John,
i use this syntax
select cast(datediff(yyyy, ACQUISITION_DATE,EXPIRATION_DATE) as varchar) + ' ' + 'YEARS : '
+ cast(datediff(mm, ACQUISITION_DATE,EXPIRATION_DATE) as varchar) + ' ' + 'MONTHS : '
+ cast(datediff(dd, ACQUISITION_DATE,EXPIRATION_DATE) as varchar) + ' ' + 'DAYS'
from mytable
and I got the result:
5 YEARS : 60 MONTHS : 1826 DAYS
thank you
January 16, 2017 at 2:58 am
Deny Christian - Monday, January 16, 2017 2:36 AMJohn Mitchell-245523 - Monday, January 16, 2017 2:13 AMUse the DATEDIFF function to get the number of years, then use DATEADD to add that to the acquisition date, then use the DATEDIFF function again to get the number of months, then use DATEADD again... I'm sure you get the picture.John
Hi John,
i use this syntax
select cast(datediff(yyyy, ACQUISITION_DATE,EXPIRATION_DATE) as varchar) + ' ' + 'YEARS : '
+ cast(datediff(mm, ACQUISITION_DATE,EXPIRATION_DATE) as varchar) + ' ' + 'MONTHS : '
+ cast(datediff(dd, ACQUISITION_DATE,EXPIRATION_DATE) as varchar) + ' ' + 'DAYS'
from mytableand I got the result:
5 YEARS : 60 MONTHS : 1826 DAYSthank you
Yes, you shouldn't be surprised at that. It gives the number of years between the two dates, the number of months between the two dates and the number of days between the two dates. That's why you need to use the approach I described above. Unfortunately, though, I've just realised that it's not even that simple. For example, notice that in the example below, the end date is one day short of five years after the start date. You might, therefore, expect the result to be the number of whole years (4) between the two dates. It's not, though - it's the number of year boundaries between the two dates, so the number of times the year changes from one to another (2012 to 2013, for example).SELECT DATEDIFF(year,'2012-03-22','2017-03-21')
I would strongly advise you to work your way through this series of articles. The part that will be of most interest to you is in the Calculating Age section of the final article. Note that the articles were written before some features that simplify date arithmetic, such as the date data type and the DATEFROMPARTS function, were available.
John
January 16, 2017 at 3:32 am
The link given below should give you some idea as well.
http://sqlhints.com/2015/07/10/how-to-get-difference-between-two-dates-in-years-months-and-days-in-sql-server/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 16, 2017 at 6:57 pm
thank you Jhon and Kingston for the link
January 19, 2017 at 1:45 am
Dear all,
I made the Stored procedure to search Years, Months, days by looping
@EXP_DATE --> Expired_Date
SELECT @@DIFF = DATEDIFF(DAY, GETDATE(),@EXP_DATE)
SELECT @@DIFF1 = DATEDIFF(DAY,@EXP_DATE,GETDATE())
IF @EXP_DATE > GETDATE()
BEGIN
SELECT @@DIFF = DATEDIFF(DAY, GETDATE(),@EXP_DATE)
WHILE @@FLG = '0'
BEGIN
IF (@@DIFF > 365)
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF/365) + ' Year, ';
SET @@DIFF = @@DIFF%365;
END
ELSE IF (@@DIFF > 30)
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF/30) + ' Month, ';
SET @@DIFF = @@DIFF%30;
END
ELSE
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF) + ' Days Left';
SET @@FLG = '1';
END
END
END
ELSE
BEGIN
SELECT @@DIFF1 = DATEDIFF(DAY,@EXP_DATE,GETDATE())
WHILE @@FLG = '0'
BEGIN
IF (@@DIFF1 > 365)
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF1/365) + ' Year, ';
SET @@DIFF1 = @@DIFF1%365;
END
ELSE IF (@@DIFF1 > 30)
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF1/30) + ' Month, ';
SET @@DIFF1 = @@DIFF1%30;
END
ELSE
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF1) + ' Days Ago';
SET @@FLG = '1';
END
END
END
January 19, 2017 at 2:12 am
Deny Christian - Thursday, January 19, 2017 1:45 AMDear all,I made the Stored procedure to search Years, Months, days by looping
@EXP_DATE --> Expired_Date
SELECT @@DIFF = DATEDIFF(DAY, GETDATE(),@EXP_DATE)
SELECT @@DIFF1 = DATEDIFF(DAY,@EXP_DATE,GETDATE())IF @EXP_DATE > GETDATE()
BEGIN
SELECT @@DIFF = DATEDIFF(DAY, GETDATE(),@EXP_DATE)
WHILE @@FLG = '0'
BEGIN
IF (@@DIFF > 365)
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF/365) + ' Year, ';
SET @@DIFF = @@DIFF%365;
ENDELSE IF (@@DIFF > 30)
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF/30) + ' Month, ';
SET @@DIFF = @@DIFF%30;
END
ELSE
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF) + ' Days Left';
SET @@FLG = '1';
ENDEND
ENDELSE
BEGIN
SELECT @@DIFF1 = DATEDIFF(DAY,@EXP_DATE,GETDATE())
WHILE @@FLG = '0'
BEGIN
IF (@@DIFF1 > 365)
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF1/365) + ' Year, ';
SET @@DIFF1 = @@DIFF1%365;
ENDELSE IF (@@DIFF1 > 30)
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF1/30) + ' Month, ';
SET @@DIFF1 = @@DIFF1%30;
END
ELSE
BEGIN
SET @@MSG = @@MSG + CONVERT(VARCHAR(MAX),@@DIFF1) + ' Days Ago';
SET @@FLG = '1';
ENDEND
END
That's an awful solution, for the following reasons:
(1) Not all years have 365 days and not all months have 30 days;
(2) You're using global variables (@@) instead of local (@). If two users happen to run the stored procedure at the same time, you'll get some very strange results;
(3) Looping is nearly always bad for performance.
Why not use the link that Kingston posted? You've got a ready-made solution there.
John
January 19, 2017 at 5:41 am
Deny Christian - Monday, January 16, 2017 1:55 AMDear Friends,I need help to count how many years,months, days left between two dates.
the data:
acquisition_date expiration_date years months days withdrawal/delay
2012-03-22 2017-03-22 ? ? ? ?how the syntax to count that data ?
thank you very much
Here's a function I worked on a year or two back, complete with test harness. Have fun.
ALTER FUNCTION [dbo].[il_ElapsedTime]
/*
Calculate the elapsed time between two datetimes
as year, month, day, hour, minute, second, millisecond
such that adding these values using DATEADD to the earlier
value will yield the later value.
*/
(@Then DATETIME, @Now DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT
y.Years,
mo.Months,
r.[Days],
r.[Hours],
r.[Minutes],
r.Seconds,
r.Milliseconds
FROM (
SELECT
DATESTRING = CONVERT(VARCHAR(8),@Then,112)+' '+CONVERT(VARCHAR(12),@Then,14),
PARMSTRING = CONVERT(VARCHAR(8),@Now,112)+' '+CONVERT(VARCHAR(12),@Now,14)
) ds
CROSS APPLY (SELECT [Years] = DATEDIFF(YEAR,@Then,@Now) - CASE WHEN SUBSTRING(DATESTRING,5,17) > SUBSTRING(PARMSTRING,5,17) THEN 1 ELSE 0 END) y
CROSS APPLY (SELECT [YearAdjDate] = DATEADD(YEAR,y.[Years],@Then)) y4
CROSS APPLY (SELECT [Months] = DATEDIFF(MONTH,y4.YearAdjDate,@Now) - CASE WHEN SUBSTRING(DATESTRING,7,15) > SUBSTRING(PARMSTRING,7,15) THEN 1 ELSE 0 END) mo
CROSS APPLY (
SELECT
[Days] = DATEDIFF(DAY,DATEADD(MONTH,mo.[Months],y4.YearAdjDate),@Now) - CASE WHEN SUBSTRING(DATESTRING,9,13) > SUBSTRING(PARMSTRING,9,13) THEN 1 ELSE 0 END,
[Hours] = DATEPART(HOUR,@Now-@Then),
[Minutes] = DATEPART(MINUTE,@Now-@Then),
[Seconds] = DATEPART(SECOND,@Now-@Then),
[Milliseconds] = DATEDIFF(MILLISECOND,DATEADD(SECOND,(CASE WHEN DATEPART(MILLISECOND,@Then) > DATEPART(MILLISECOND,@Now) THEN -1 ELSE 0 END),DATEADD(MILLISECOND,DATEPART(MILLISECOND,@Then),DATEADD(MILLISECOND,0-DATEPART(MILLISECOND,@Now),@Now))),@Now)
) r
WHERE @Now > @Then
-- Testing
-- 42,963 rows / 00:00:01 including generating sample data,
-- reconstituting now from then and output to screen
DECLARE @Now DATETIME = GETDATE()-18;
WITH SampleData AS (
SELECT TOP(211+DATEDIFF(DAY,'19000101',GETDATE()))
[Then] = DATEADD(MILLISECOND,ABS(CHECKSUM(NEWID()))%86400000,DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,'19000101'))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) h (n)
)
SELECT
s.[Then],
[Now] = @Now,
q.*
,c.Calc
FROM SampleData s
OUTER APPLY dbo.il_ElapsedTime (s.[Then], @Now) q
-- include this for testing only
CROSS APPLY (
SELECT Calc =
DATEADD(MILLISECOND,q.[Milliseconds],
DATEADD(SECOND,q.[Seconds],
DATEADD(MINUTE,q.[Minutes],
DATEADD(HOUR,q.[Hours],
DATEADD(DAY,q.[days],
DATEADD(MONTH,q.months,
DATEADD(YEAR,q.Years,s.[Then]))))))) ) c
WHERE @Now <> Calc
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 20, 2017 at 10:31 am
Deny Christian - Monday, January 16, 2017 1:55 AMDear Friends,I need help to count how many years,months, days left between two dates.
the data:
acquisition_date expiration_date years months days withdrawal/delay
2012-03-22 2017-03-22 ? ? ? ?how the syntax to count that data ?
thank you very much
I would handle this in the front end. Various industries have a very different definition of what a month is. For example, in the financial world, a month is often 30 days. By definition! But if you use the common error calendar months of 28, 29, 30 or 31 days, the rules for going from the middle of one calendar month to a date inside another calendar month vary. The one thing all the calendars agree on (thanks to mother nature!), Is it a day is a unit of measure. And that is very easy to compute with a date difference function.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 24, 2017 at 7:53 am
playing with dates, see if it helps
;WITH DIFF AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '20170130', '20170425'), 0) d
)
, PARTS AS (
SELECT DATEDIFF(YEAR, 0, D) AS y
, DATEDIFF(MONTH, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, d) * -1, d)) + 1 AS m
, DATEDIFF(DAY, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, d) * -1, d)) - 1 AS d
FROM DIFF
)
SELECT y, m, d
, CAST(y AS VARCHAR(4)) + ' Years ' + CAST(m AS VARCHAR(2)) + ' Months ' + CAST(d AS VARCHAR(2)) + ' Days.' AS Diff
, CASE
WHEN y > 1 THEN CAST(y AS VARCHAR(4)) + ' Years '
ELSE
CASE
WHEN y = 1 THEN CAST(y AS VARCHAR(4)) + ' Year '
ELSE ''
END
END
+ CASE
WHEN m > 1 THEN CAST(m AS VARCHAR(2)) + ' Months '
ELSE
CASE
WHEN m = 1 THEN CAST(m AS VARCHAR(4)) + ' Month '
ELSE ''
END
END
+ CASE
WHEN d > 1 THEN CAST(d AS VARCHAR(2)) + ' Days '
ELSE
CASE
WHEN d = 1 THEN CAST(d AS VARCHAR(4)) + ' Day '
ELSE ''
END
END AS Diff2
FROM PARTS
January 25, 2017 at 8:27 pm
Dear all,
thank you so much for the explaining and the syntax.
Now i have options to choose..
January 25, 2017 at 8:59 pm
ChrisM@Work - Thursday, January 19, 2017 5:41 AMDeny Christian - Monday, January 16, 2017 1:55 AMDear Friends,I need help to count how many years,months, days left between two dates.
the data:
acquisition_date expiration_date years months days withdrawal/delay
2012-03-22 2017-03-22 ? ? ? ?how the syntax to count that data ?
thank you very much
Here's a function I worked on a year or two back, complete with test harness. Have fun.
ALTER FUNCTION [dbo].[il_ElapsedTime]
/*
Calculate the elapsed time between two datetimes
as year, month, day, hour, minute, second, millisecond
such that adding these values using DATEADD to the earlier
value will yield the later value.
*/
(@Then DATETIME, @Now DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT
y.Years,
mo.Months,
r.[Days],
r.[Hours],
r.[Minutes],
r.Seconds,
r.Milliseconds
FROM (
SELECT
DATESTRING = CONVERT(VARCHAR(8),@Then,112)+' '+CONVERT(VARCHAR(12),@Then,14),
PARMSTRING = CONVERT(VARCHAR(8),@Now,112)+' '+CONVERT(VARCHAR(12),@Now,14)
) ds
CROSS APPLY (SELECT [Years] = DATEDIFF(YEAR,@Then,@Now) - CASE WHEN SUBSTRING(DATESTRING,5,17) > SUBSTRING(PARMSTRING,5,17) THEN 1 ELSE 0 END) y
CROSS APPLY (SELECT [YearAdjDate] = DATEADD(YEAR,y.[Years],@Then)) y4
CROSS APPLY (SELECT [Months] = DATEDIFF(MONTH,y4.YearAdjDate,@Now) - CASE WHEN SUBSTRING(DATESTRING,7,15) > SUBSTRING(PARMSTRING,7,15) THEN 1 ELSE 0 END) mo
CROSS APPLY (
SELECT
[Days] = DATEDIFF(DAY,DATEADD(MONTH,mo.[Months],y4.YearAdjDate),@Now) - CASE WHEN SUBSTRING(DATESTRING,9,13) > SUBSTRING(PARMSTRING,9,13) THEN 1 ELSE 0 END,
[Hours] = DATEPART(HOUR,@Now-@Then),
[Minutes] = DATEPART(MINUTE,@Now-@Then),
[Seconds] = DATEPART(SECOND,@Now-@Then),
[Milliseconds] = DATEDIFF(MILLISECOND,DATEADD(SECOND,(CASE WHEN DATEPART(MILLISECOND,@Then) > DATEPART(MILLISECOND,@Now) THEN -1 ELSE 0 END),DATEADD(MILLISECOND,DATEPART(MILLISECOND,@Then),DATEADD(MILLISECOND,0-DATEPART(MILLISECOND,@Now),@Now))),@Now)
) r
WHERE @Now > @Then
-- Testing
-- 42,963 rows / 00:00:01 including generating sample data,
-- reconstituting now from then and output to screen
DECLARE @Now DATETIME = GETDATE()-18;
WITH SampleData AS (
SELECT TOP(211+DATEDIFF(DAY,'19000101',GETDATE()))
[Then] = DATEADD(MILLISECOND,ABS(CHECKSUM(NEWID()))%86400000,DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,'19000101'))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) h (n)
)
SELECT
s.[Then],
[Now] = @Now,
q.*
,c.Calc
FROM SampleData s
OUTER APPLY dbo.il_ElapsedTime (s.[Then], @Now) q
-- include this for testing only
CROSS APPLY (
SELECT Calc =
DATEADD(MILLISECOND,q.[Milliseconds],
DATEADD(SECOND,q.[Seconds],
DATEADD(MINUTE,q.[Minutes],
DATEADD(HOUR,q.[Hours],
DATEADD(DAY,q.[days],
DATEADD(MONTH,q.months,
DATEADD(YEAR,q.Years,s.[Then]))))))) ) c
WHERE @Now <> Calc
Brilliant stuff Chris!
-- Itzik Ben-Gan 2001
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply