How to calculate period in T-SQL with years, months, and days. If less than ....

Question

Post reply

How to calculate period in T-SQL with years, months, and days. If less than ....

Adelia

SSC-Addicted

Points: 422

August 26, 2021 at 1:35 am

Go to Answer

#3924081

I've this ( years, months, and days )

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '5/8/2019'


SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

/*
(No column name)(No column name)(No column name)
-------------------------------------------------------------------------
2                  3                  18

*/

If less than day, how to calculate in Hours, Minute and Seconds ?

I wanna display something like this,

11 hours and 33 minutes ago

47 minutes ago

30 second ago

Please help

This topic was modified 2 years, 8 months ago by Adelia.

Sergiy

SSC Guru

Points: 110207
More actions
August 30, 2021 at 5:32 am
Answer
#3924917
Adelia, what are you're struggling with?
You've been given a function which gives you all the right numbers, all you need is to incorporate those numbers into a string of the required formatting.
Something klike that:
```
DECLARE @currentDate2 datetime 
SET @currentDate2 = GETDATE() 
SELECT *,
CASE Days WHEN 0 then '' ELSE 
convert(varchar(5), Days) + CASE DAYS WHEN 1 then 'Day, ' ELSE ' Days, 'END 
END + 
CASE WHEN Hours > 0 then convert(varchar(5), Hours) + ' Hours, ' ELSE '' END + 
CASE convert(varchar(5), Minutes) + ' Minutes ago'
FROM [dbo].[IncidentSimulation]
CROSS APPLY Service.[dbo].[AgeCalculation] ([IncidentDate], @currentDate2 ,'DDHHNN')
```
I used some assumptions of my own, as your requirements are not quite specific.
Use parts of this script as templates and form whatever string you're required.
_____________
Code for TallyGenerator

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Calculate the minutes and seconds the same way you did the years, months and days - using DATEDIFF. Formatting of the type you describe is best done in the presentation layer, in my opinion. If you have to do it in T-SQL, you'll need to use a series of CASE expressions to determine which date parts to display, then concatenate them all together. Have a go at that, and post back here if you're struggling.

John

Sergiy SSC Guru Points: 110207 More actions · Answer 2

This should show you how:

Article on age/duration calculation

_____________
Code for TallyGenerator

Adelia SSC-Addicted Points: 422 More actions · Answer 3

Sergiy wrote:

This should show you how:
Article on age/duration calculation

Hi Sergiy,

Just created your Tally Generator. Consider this,

DECLARE @date1 datetime, @currentDate1 datetime
SET @date1 = '8/26/2019 06:23:45 PM'
Set @currentDate1 = getdate()

select @currentDate1 - @date1


--Hours format
--3 Hours, 20 Minutes Ago



DECLARE @date2 datetime, @currentDate2 datetime
SET @date2 = '8/24/2019 03:41:12 PM'
Set @currentDate2 = getdate()

select @currentDate2 - @date2


--Days format
--2 Days, 7 Minutes Ago

How to get,

Hours, Minutes format
Days, Minutes format
Weeks, Days format
Months, Days format
Years, Months format

If possible, can you show me the way?

Sergiy SSC Guru Points: 110207 More actions · Answer 4

SELECT *, 
CASE WHEN Days > 0 then convert(varchar(10), Days) + ' Days, ' ELSE '' END
+ CASE WHEN Hours > 0 then convert(varchar(10), Hours) + ' Hours, ' ELSE '' END
+ CASE WHEN Minutes > 0 then convert(varchar(10), Minutes) + ' Minutes' ELSE '' END
FROM [dbo].[AgeCalculation] (@date2, @currentDate2 ,'YYDDHHNN')

_____________
Code for TallyGenerator

Adelia SSC-Addicted Points: 422 More actions · Answer 5

John Mitchell-245523 wrote:

Calculate the minutes and seconds the same way you did the years, months and days - using DATEDIFF. Formatting of the type you describe is best done in the presentation layer, in my opinion. If you have to do it in T-SQL, you'll need to use a series of CASE expressions to determine which date parts to display, then concatenate them all together. Have a go at that, and post back here if you're struggling.
John

Hello John,

I'm struggling. Need your help. Consider this simple table

CREATE TABLE [dbo].[IncidentSimulation](
[Id] [int] IDENTITY(1,1) NOT NULL,
[IncidentDate] [datetime] NULL,
 CONSTRAINT [PK_IncidentSimulation] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[IncidentSimulation] ON 
GO
INSERT [dbo].[IncidentSimulation] ([Id], [IncidentDate]) VALUES (1, CAST(N'2021-07-12T23:00:00.000' AS DateTime))
GO
INSERT [dbo].[IncidentSimulation] ([Id], [IncidentDate]) VALUES (2, CAST(N'2021-08-03T01:30:00.000' AS DateTime))
GO
INSERT [dbo].[IncidentSimulation] ([Id], [IncidentDate]) VALUES (3, CAST(N'2021-08-03T01:30:00.000' AS DateTime))
GO
INSERT [dbo].[IncidentSimulation] ([Id], [IncidentDate]) VALUES (4, CAST(N'2021-08-02T07:00:00.000' AS DateTime))
GO
INSERT [dbo].[IncidentSimulation] ([Id], [IncidentDate]) VALUES (5, CAST(N'2021-08-03T03:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[IncidentSimulation] OFF
GO

I want to produce to format only. Hours, Minutes ago and Days, Minutes ago

Select ID, 
IncidentDate, 
Case 
When DATEDIFF(day, IncidentDate, getdate()) < 0 Then 'Hours, Minute ago'
When DATEDIFF(day, IncidentDate, getdate()) > 0 Then 'Days, Minute ago'
End as HowLong
from [dbo].[IncidentSimulation];

Minute display must less than 60 minutes. The output something like this,

14 Days, 7 Minute ago
21 Days, 34 Minute ago
45 Minute ago

pietlinden SSC Guru Points: 63436 More actions · Answer 6

use datediff and calc full days first, then subtract, then repeat for hours, and then minutes?

Adelia SSC-Addicted Points: 422 More actions · Answer 7

Sergiy wrote:

Adelia, what are you're struggling with?
You've been given a function which gives you all the right numbers, all you need is to incorporate those numbers into a string of the required formatting.
Something klike that:
DECLARE @currentDate2 datetime 
SET @currentDate2 = GETDATE() 
SELECT *,
CASE Days WHEN 0 then '' ELSE 
convert(varchar(5), Days) + CASE DAYS WHEN 1 then 'Day, ' ELSE ' Days, 'END 
END + 
CASE WHEN Hours > 0 then convert(varchar(5), Hours) + ' Hours, ' ELSE '' END + 
CASE convert(varchar(5), Minutes) + ' Minutes ago'
FROM [dbo].[IncidentSimulation]
CROSS APPLY Service.[dbo].[AgeCalculation] ([IncidentDate], @currentDate2 ,'DDHHNN')
I used some assumptions of my own, as your requirements are not quite specific.
Use parts of this script as templates and form whatever string you're required.

Hi Sergiy,

Where to AgeCalculation ?

CROSS APPLY Service.[dbo].[AgeCalculation]

Sergiy SSC Guru Points: 110207 More actions · Answer 8