# Calculate days depending on hours

### Question

• i have field with total hours (45:20) and our working hours is 7 hours per day and i need to calculate how many days will it be so we can calculate the salary, how can we do it in sql server query?
Thursday, December 6, 2018 12:10 PM

• Hi FarmerIT,

Per your description, I am sorry that I could not understand what 'remaining hours and minutes' means clearly.

Would you like this one?

```declare @t table
(
s varchar(10)
)
insert @t
values('45:20')

SELECT cast(LEFT(s,CHARINDEX(':',s)-1)/7 as varchar(5))+'days '
+cast(LEFT(s,CHARINDEX(':',s)-1)%7 as varchar(5))+'hours '
+STUFF(s,1,CHARINDEX(':',s),'')+'minutes ' as result
FROM @t
/*
result
-----------------------------
6days 3hours 20minutes
*/```

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Friday, December 7, 2018 11:17 PM
Friday, December 7, 2018 3:05 AM

### All replies

• 45 divide by 7?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Thursday, December 6, 2018 12:21 PM
• see this illustration

```declare @t table
(
s varchar(10)
)
insert @t
values('45:20')

SELECT CEILING((LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),''))/(7.0 * 60.0)) AS DaysWorked
FROM @t

/*
Output
---------------------------------------------
DaysWorked
-----------------
7

*/```

If decimal is allowed you can use this

```declare @t table
(
s varchar(10)
)
insert @t
values('45:20')

SELECT CAST((LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),''))/(7.0 * 60.0) AS decimal(30,2)) AS DaysWorked
FROM @t

/*
Output
------------------------------
DaysWorked
-----------------------
6.48

*/```

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

• Edited by Thursday, December 6, 2018 12:38 PM
Thursday, December 6, 2018 12:36 PM
• i need to keep the remaining hours and minutes for the next month. how can i get that also.
Thursday, December 6, 2018 9:25 PM
• Hi FarmerIT,

Per your description, I am sorry that I could not understand what 'remaining hours and minutes' means clearly.

Would you like this one?

```declare @t table
(
s varchar(10)
)
insert @t
values('45:20')

SELECT cast(LEFT(s,CHARINDEX(':',s)-1)/7 as varchar(5))+'days '
+cast(LEFT(s,CHARINDEX(':',s)-1)%7 as varchar(5))+'hours '
+STUFF(s,1,CHARINDEX(':',s),'')+'minutes ' as result
FROM @t
/*
result
-----------------------------
6days 3hours 20minutes
*/```

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Friday, December 7, 2018 11:17 PM
Friday, December 7, 2018 3:05 AM
• thanks that's helped a lot,

can i ask you another thing,

if i want to add these hours and minutes to the new month calculation how can i do that for example

33:20 + 3 hours + 20 minutes

Sunday, December 9, 2018 9:07 AM
• i need to keep the remaining hours and minutes for the next month. how can i get that also.

like

this

```declare @t table
(
s varchar(10)
)
insert @t
values('45:20')

SELECT (LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),''))/(7 * 60) AS DaysWorked,
FORMAT(DATEADD(minute,(LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),''))%(7 * 60),0),'hh:mm') AS Residual
FROM @t

/*
Output
-----------------------------------------------------------
DaysWorked	Residual
-----------------------------
6	        03:20
*/```

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Sunday, December 9, 2018 10:56 AM
• thanks that's helped a lot,

can i ask you another thing,

if i want to add these hours and minutes to the new month calculation how can i do that for example

33:20 + 3 hours + 20 minutes

you need to do this as per below illustration

```declare @t table
(
s varchar(10)
)
insert @t
values('33:20')

declare @m varchar(10) = '3:20'

SELECT CONCAT(((LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),'')) +  (LEFT(@m,CHARINDEX(':',@m)-1) * 60 + STUFF(@m,1,CHARINDEX(':',@m),'')))/60,':',((LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),'')) +  (LEFT(@m,CHARINDEX(':',@m)-1) * 60 + STUFF(@m,1,CHARINDEX(':',@m),'')))%60)
FROM @t/*Output---------------36:40*/```

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Sunday, December 9, 2018 11:03 AM
• ```declare @t table
(
s varchar(10)
)
insert @t
values('33:20')

declare @m varchar(10) = '3:20'

SELECT cast((cast(LEFT(s,CHARINDEX(':',s)-1) as int)+cast(LEFT(@m,CHARINDEX(':',@m)-1) as int))/7 as varchar(5))+'days '
+cast((cast(LEFT(s,CHARINDEX(':',s)-1) as int)+cast(LEFT(@m,CHARINDEX(':',@m)-1) as int))%7 as varchar(5))+'hours '
+cast((cast(STUFF(s,1,CHARINDEX(':',s),'') as int)+cast(STUFF(@m,1,CHARINDEX(':',@m),'') as int))as varchar(5))+'minutes ' as result
FROM @t
/*
result
----------------------------------
5days 1hours 40minutes
*/```

Best  Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, December 10, 2018 7:57 AM