Mixing
Return time to end of next day
1
I have two columns: Start_Date and End_Date.
The task is to return a time to end of next day (using T-SQL, SSMS 14.0).
For example:
Start_Date = '2018-11-21 23:40:00' End_Date = '2018-11-23 02:40:00'
The query should return 3 rows:
0 hour 20 min -- time between 2018-11-21 23:40:00 and 2018-11-22 00:00:00
24 hour 0 min -- time between 2018-11-22 00:00:00 and 2018-11-23 00:00:00
2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00
I suppose it can be implemented using the cursor or recursive CTE.
sql
sql-server tsql asked Nov 21 '18 at 20:28
ValentynValentyn
469
|
show 10 more comments
1
I have two columns: Start_Date and End_Date.
The task is to return a time to end of next day (using T-SQL, SSMS 14.0).
For example:
Start_Date = '2018-11-21 23:40:00' End_Date = '2018-11-23 02:40:00'
The query should return 3 rows:
0 hour 20 min -- time between 2018-11-21 23:40:00 and 2018-11-22 00:00:00
24 hour 0 min -- time between 2018-11-22 00:00:00 and 2018-11-23 00:00:00
2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00
I suppose it can be implemented using the cursor or recursive CTE.
sql
sql-server tsql asked Nov 21 '18 at 20:28
ValentynValentyn
469
1
The task is not hard but probably you need to explain more like what is the logic? and why it can't be done?
– ViKiNG
Nov 21 '18 at 20:34
@ViKiNG Because I have no idea how to remove time (set 00:00:00 if to say correctly) for next days
– Valentyn
Nov 21 '18 at 20:44
2
@HimanshuAhuja Did you see Oracle tag there?
– Sami
Nov 21 '18 at 21:16
1
@HimanshuAhuja From where did you get rownum?
– Sami
Nov 21 '18 at 21:33
1
@HimanshuAhuja Simply, that won't return 3 rows as OP wanted, and it won't work anyway.
– Sami
Nov 21 '18 at 21:45
|
show 10 more comments
1
1
1
I have two columns: Start_Date and End_Date.
The task is to return a time to end of next day (using T-SQL, SSMS 14.0).
For example:
Start_Date = '2018-11-21 23:40:00' End_Date = '2018-11-23 02:40:00'
The query should return 3 rows:
0 hour 20 min -- time between 2018-11-21 23:40:00 and 2018-11-22 00:00:00
24 hour 0 min -- time between 2018-11-22 00:00:00 and 2018-11-23 00:00:00
2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00
I suppose it can be implemented using the cursor or recursive CTE.
sql
sql-server tsql asked Nov 21 '18 at 20:28
ValentynValentyn
469
I have two columns: Start_Date and End_Date.
The task is to return a time to end of next day (using T-SQL, SSMS 14.0).
For example:
Start_Date = '2018-11-21 23:40:00' End_Date = '2018-11-23 02:40:00'
The query should return 3 rows:
0 hour 20 min -- time between 2018-11-21 23:40:00 and 2018-11-22 00:00:00
24 hour 0 min -- time between 2018-11-22 00:00:00 and 2018-11-23 00:00:00
2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00
I suppose it can be implemented using the cursor or recursive CTE.
sql
sql-server tsql sql
sql-server tsql asked Nov 21 '18 at 20:28
ValentynValentyn
469
asked Nov 21 '18 at 20:28
ValentynValentyn
469
asked Nov 21 '18 at 20:28
ValentynValentyn
469
asked Nov 21 '18 at 20:28
ValentynValentyn
469
asked Nov 21 '18 at 20:28
ValentynValentyn
469
469
1
The task is not hard but probably you need to explain more like what is the logic? and why it can't be done?
– ViKiNG
Nov 21 '18 at 20:34
@ViKiNG Because I have no idea how to remove time (set 00:00:00 if to say correctly) for next days
– Valentyn
Nov 21 '18 at 20:44
2
@HimanshuAhuja Did you see Oracle tag there?
– Sami
Nov 21 '18 at 21:16
1
@HimanshuAhuja From where did you get rownum?
– Sami
Nov 21 '18 at 21:33
1
@HimanshuAhuja Simply, that won't return 3 rows as OP wanted, and it won't work anyway.
– Sami
Nov 21 '18 at 21:45
|
show 10 more comments
1
The task is not hard but probably you need to explain more like what is the logic? and why it can't be done?
– ViKiNG
Nov 21 '18 at 20:34
@ViKiNG Because I have no idea how to remove time (set 00:00:00 if to say correctly) for next days
– Valentyn
Nov 21 '18 at 20:44
2
@HimanshuAhuja Did you see Oracle tag there?
– Sami
Nov 21 '18 at 21:16
1
@HimanshuAhuja From where did you get rownum?
– Sami
Nov 21 '18 at 21:33
1
@HimanshuAhuja Simply, that won't return 3 rows as OP wanted, and it won't work anyway.
– Sami
Nov 21 '18 at 21:45
1
1
The task is not hard but probably you need to explain more like what is the logic? and why it can't be done?
– ViKiNG
Nov 21 '18 at 20:34
The task is not hard but probably you need to explain more like what is the logic? and why it can't be done?
– ViKiNG
Nov 21 '18 at 20:34
@ViKiNG Because I have no idea how to remove time (set 00:00:00 if to say correctly) for next days
– Valentyn
Nov 21 '18 at 20:44
@ViKiNG Because I have no idea how to remove time (set 00:00:00 if to say correctly) for next days
– Valentyn
Nov 21 '18 at 20:44
2
2
@HimanshuAhuja Did you see Oracle tag there?
– Sami
Nov 21 '18 at 21:16
@HimanshuAhuja Did you see Oracle tag there?
– Sami
Nov 21 '18 at 21:16
1
1
@HimanshuAhuja From where did you get rownum?
– Sami
Nov 21 '18 at 21:33
@HimanshuAhuja From where did you get rownum?
– Sami
Nov 21 '18 at 21:33
1
1
@HimanshuAhuja Simply, that won't return 3 rows as OP wanted, and it won't work anyway.
– Sami
Nov 21 '18 at 21:45
@HimanshuAhuja Simply, that won't return 3 rows as OP wanted, and it won't work anyway.
– Sami
Nov 21 '18 at 21:45
|
show 10 more comments
3 Answers
3
active
oldest
votes
1
Yes, CTE can be used with contribution of DATEDIFF and DATEADD functions together as :
CREATE VIEW DAYS AS
WITH CTE AS (
SELECT 1 as nr
UNION ALL
SELECT nr+1
FROM CTE
WHERE nr <= DATEDIFF( day, CONVERT(date, '2018-11-21 23:40:00'),
CONVERT(date, '2018-11-23 02:40:00') )
)
SELECT
DATEDIFF(
minute,
(case when nr > 1 then
DATEADD ( day , nr - 1 , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 1 , CONVERT(datetime, '2018-11-21 23:40:00'))
end),
(case when nr < 3 then
DATEADD ( day , nr , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 3, CONVERT(datetime, '2018-11-23 02:40:00'))
end)
) as time_diff
FROM CTE;
SELECT cast(time_diff/60 as varchar) + ' hours '
+ cast(time_diff % 60 as varchar) + ' minutes' as "Time Difference" FROM DAYS;
Time Difference
-------------------
0 hours 20 minutes
24 hours 0 minutes
2 hours 40 minutes
dbfiddle demo
answered Nov 21 '18 at 22:19
Barbaros ÖzhanBarbaros Özhan
13.5k71633
From OP question last row should return "2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00"
– Sami
Nov 21 '18 at 22:24
@Sami ohh, didn't pay attention while going to bed :), let me try little more, thanks.
– Barbaros Özhan
Nov 21 '18 at 22:27
1
Yeah, it happen, but still we have the edit button there ;)
– Sami
Nov 21 '18 at 23:20
add a comment |
0
You can get your desired results as
WITH Dates AS
(
SELECT 1 RN,
@sd StartD,
CAST(DATEADD(Day, -1, @ed) AS DATE) EndD
UNION
SELECT 2,
CAST(DATEADD(Day, 1, @sd) AS DATE),
CAST(@ed AS DATE)
UNION
SELECT 3,
CAST(@ed AS DATE),
@ed
)
SELECT
CAST( DATEDIFF(YEAR, StartD, EndD) AS VARCHAR(10) )+ ' Years, '+
CAST( DATEDIFF(MONTH, StartD, EndD)%12 AS VARCHAR(10) )+ ' Months, '+
CAST( (DATEDIFF(DAY, StartD, EndD)%31)%12
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) )+ ' Days, '+
CAST( (DATEDIFF(HOUR, StartD, EndD)%60)%24
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) ) + ' Hours,'+
CAST( DATEDIFF(MINUTE, StartD, EndD)%60 AS VARCHAR(10) ) +' Minutes, '+
CAST( DATEDIFF(SECOND, StartD, EndD)%60 AS VARCHAR(10) ) +' Seconds' AS Results
FROM Dates;
Results:
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 0 Days, 0 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
Demo
Finally, as a note I think your results should be
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 1 Days, 1 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
if I'm right, then all what you need to do is removing the two (2) CASE expressions.
answered Nov 21 '18 at 23:07
SamiSami
8,94331241
add a comment |
0
I believe you will want the ability to apply this to a variety of task durations. Here I have used a dynamic "tally table" which just a series of numbers from 0 to (in this case) 100, if you need a greater range amend the Tally cte by adding extra cross joins. If you already have a table of numbers, or your own preferred method for this substitute that for mine.
CREATE TABLE mytable(
ID Integer NOT NULL
,Start_Date datetime NOT NULL
,End_Date datetime NOT NULL
);
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (1,'2018-11-21 23:40:00','2018-11-23 02:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (2,'2018-11-23 13:40:00','2018-11-23 22:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (3,'2018-11-18 23:40:00','2018-11-23 02:40:00');
3 test cases, as given, + a very short duration + a longer duration:
;WITH
Digits AS (
SELECT 0 AS digit UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
)
, Tally AS (
SELECT [ones].digit
+ [tens].digit * 10
-- + [hundreds].digit * 100
AS number
FROM Digits [ones]
CROSS JOIN Digits [tens]
-- CROSS JOIN Digits [hundreds]
)
select
ID
, cast(ca2.minutes/60 as varchar) + ' hours '
+ cast(ca2.minutes % 60 as varchar) + ' minutes' as Duration
, format(on_date,'yyyy-MM-dd') on_date
, portion
, format(start_date,'yyyy-MM-dd hh:mm') start_date
, format(end_date,'yyyy-MM-dd hh:mm') end_date
from mytable t
inner join Tally on Tally.number <= datediff(dd,t.Start_Date,t.End_Date)
cross apply (
select dateadd(dd,tally.number,dateadd(dd,datediff(dd,0,t.start_date),0)) on_date
) ca
cross apply (
select
case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then datediff(minute,t.start_date,t.end_date)
when cast(t.end_date as date) = ca.on_date then datediff(minute,ca.on_date,t.end_date)
when ca.on_date between t.start_date and t.end_date then 24*60
when ca.on_date < t.start_date then datediff(minute,t.start_date,dateadd(dd,1,ca.on_date))
end minutes
, case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then 'is on'
when cast(t.end_date as date) = ca.on_date then 'end'
when ca.on_date between t.start_date and t.end_date then 'span'
when ca.on_date < t.start_date then 'start'
end portion
) ca2
I have used 2 apply operators, the first turns a number from the Tally into a date, and this is then re-used by the alias on_date in the second apply where we can calculate the duration. Note the second case expression portion is not essential, it's just there to help document the logic.
Result:
ID Duration on_date portion start_date end_date
---- ---- -------------------- ------------ --------- ------------------ ------------------
1 1 0 hours 20 minutes 2018-11-21 start 2018-11-21 11:40 2018-11-23 02:40
2 1 24 hours 0 minutes 2018-11-22 span 2018-11-21 11:40 2018-11-23 02:40
3 1 2 hours 40 minutes 2018-11-23 end 2018-11-21 11:40 2018-11-23 02:40
4 2 9 hours 0 minutes 2018-11-23 is on 2018-11-23 01:40 2018-11-23 10:40
5 3 0 hours 20 minutes 2018-11-18 start 2018-11-18 11:40 2018-11-23 02:40
6 3 24 hours 0 minutes 2018-11-19 span 2018-11-18 11:40 2018-11-23 02:40
7 3 24 hours 0 minutes 2018-11-20 span 2018-11-18 11:40 2018-11-23 02:40
8 3 24 hours 0 minutes 2018-11-21 span 2018-11-18 11:40 2018-11-23 02:40
9 3 24 hours 0 minutes 2018-11-22 span 2018-11-18 11:40 2018-11-23 02:40
10 3 2 hours 40 minutes 2018-11-23 end 2018-11-18 11:40 2018-11-23 02:40
also see: https://rextester.com/VPTI85082
answered Nov 21 '18 at 23:29
Used_By_AlreadyUsed_By_Already
23k22038
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420014%2freturn-time-to-end-of-next-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Yes, CTE can be used with contribution of DATEDIFF and DATEADD functions together as :
CREATE VIEW DAYS AS
WITH CTE AS (
SELECT 1 as nr
UNION ALL
SELECT nr+1
FROM CTE
WHERE nr <= DATEDIFF( day, CONVERT(date, '2018-11-21 23:40:00'),
CONVERT(date, '2018-11-23 02:40:00') )
)
SELECT
DATEDIFF(
minute,
(case when nr > 1 then
DATEADD ( day , nr - 1 , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 1 , CONVERT(datetime, '2018-11-21 23:40:00'))
end),
(case when nr < 3 then
DATEADD ( day , nr , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 3, CONVERT(datetime, '2018-11-23 02:40:00'))
end)
) as time_diff
FROM CTE;
SELECT cast(time_diff/60 as varchar) + ' hours '
+ cast(time_diff % 60 as varchar) + ' minutes' as "Time Difference" FROM DAYS;
Time Difference
-------------------
0 hours 20 minutes
24 hours 0 minutes
2 hours 40 minutes
dbfiddle demo
answered Nov 21 '18 at 22:19
Barbaros ÖzhanBarbaros Özhan
13.5k71633
From OP question last row should return "2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00"
– Sami
Nov 21 '18 at 22:24
@Sami ohh, didn't pay attention while going to bed :), let me try little more, thanks.
– Barbaros Özhan
Nov 21 '18 at 22:27
1
Yeah, it happen, but still we have the edit button there ;)
– Sami
Nov 21 '18 at 23:20
add a comment |
1
Yes, CTE can be used with contribution of DATEDIFF and DATEADD functions together as :
CREATE VIEW DAYS AS
WITH CTE AS (
SELECT 1 as nr
UNION ALL
SELECT nr+1
FROM CTE
WHERE nr <= DATEDIFF( day, CONVERT(date, '2018-11-21 23:40:00'),
CONVERT(date, '2018-11-23 02:40:00') )
)
SELECT
DATEDIFF(
minute,
(case when nr > 1 then
DATEADD ( day , nr - 1 , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 1 , CONVERT(datetime, '2018-11-21 23:40:00'))
end),
(case when nr < 3 then
DATEADD ( day , nr , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 3, CONVERT(datetime, '2018-11-23 02:40:00'))
end)
) as time_diff
FROM CTE;
SELECT cast(time_diff/60 as varchar) + ' hours '
+ cast(time_diff % 60 as varchar) + ' minutes' as "Time Difference" FROM DAYS;
Time Difference
-------------------
0 hours 20 minutes
24 hours 0 minutes
2 hours 40 minutes
dbfiddle demo
answered Nov 21 '18 at 22:19
Barbaros ÖzhanBarbaros Özhan
13.5k71633
From OP question last row should return "2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00"
– Sami
Nov 21 '18 at 22:24
@Sami ohh, didn't pay attention while going to bed :), let me try little more, thanks.
– Barbaros Özhan
Nov 21 '18 at 22:27
1
Yeah, it happen, but still we have the edit button there ;)
– Sami
Nov 21 '18 at 23:20
add a comment |
1
1
1
Yes, CTE can be used with contribution of DATEDIFF and DATEADD functions together as :
CREATE VIEW DAYS AS
WITH CTE AS (
SELECT 1 as nr
UNION ALL
SELECT nr+1
FROM CTE
WHERE nr <= DATEDIFF( day, CONVERT(date, '2018-11-21 23:40:00'),
CONVERT(date, '2018-11-23 02:40:00') )
)
SELECT
DATEDIFF(
minute,
(case when nr > 1 then
DATEADD ( day , nr - 1 , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 1 , CONVERT(datetime, '2018-11-21 23:40:00'))
end),
(case when nr < 3 then
DATEADD ( day , nr , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 3, CONVERT(datetime, '2018-11-23 02:40:00'))
end)
) as time_diff
FROM CTE;
SELECT cast(time_diff/60 as varchar) + ' hours '
+ cast(time_diff % 60 as varchar) + ' minutes' as "Time Difference" FROM DAYS;
Time Difference
-------------------
0 hours 20 minutes
24 hours 0 minutes
2 hours 40 minutes
dbfiddle demo
answered Nov 21 '18 at 22:19
Barbaros ÖzhanBarbaros Özhan
13.5k71633
Yes, CTE can be used with contribution of DATEDIFF and DATEADD functions together as :
CREATE VIEW DAYS AS
WITH CTE AS (
SELECT 1 as nr
UNION ALL
SELECT nr+1
FROM CTE
WHERE nr <= DATEDIFF( day, CONVERT(date, '2018-11-21 23:40:00'),
CONVERT(date, '2018-11-23 02:40:00') )
)
SELECT
DATEDIFF(
minute,
(case when nr > 1 then
DATEADD ( day , nr - 1 , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 1 , CONVERT(datetime, '2018-11-21 23:40:00'))
end),
(case when nr < 3 then
DATEADD ( day , nr , CONVERT(date, '2018-11-21 23:40:00') )
else
DATEADD ( day , nr - 3, CONVERT(datetime, '2018-11-23 02:40:00'))
end)
) as time_diff
FROM CTE;
SELECT cast(time_diff/60 as varchar) + ' hours '
+ cast(time_diff % 60 as varchar) + ' minutes' as "Time Difference" FROM DAYS;
Time Difference
-------------------
0 hours 20 minutes
24 hours 0 minutes
2 hours 40 minutes
dbfiddle demo
answered Nov 21 '18 at 22:19
Barbaros ÖzhanBarbaros Özhan
13.5k71633
edited Nov 21 '18 at 22:33
answered Nov 21 '18 at 22:19
Barbaros ÖzhanBarbaros Özhan
13.5k71633
answered Nov 21 '18 at 22:19
Barbaros ÖzhanBarbaros Özhan
13.5k71633
answered Nov 21 '18 at 22:19
Barbaros ÖzhanBarbaros Özhan
13.5k71633
13.5k71633
From OP question last row should return "2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00"
– Sami
Nov 21 '18 at 22:24
@Sami ohh, didn't pay attention while going to bed :), let me try little more, thanks.
– Barbaros Özhan
Nov 21 '18 at 22:27
1
Yeah, it happen, but still we have the edit button there ;)
– Sami
Nov 21 '18 at 23:20
add a comment |
From OP question last row should return "2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00"
– Sami
Nov 21 '18 at 22:24
@Sami ohh, didn't pay attention while going to bed :), let me try little more, thanks.
– Barbaros Özhan
Nov 21 '18 at 22:27
1
Yeah, it happen, but still we have the edit button there ;)
– Sami
Nov 21 '18 at 23:20
From OP question last row should return "2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00"
– Sami
Nov 21 '18 at 22:24
From OP question last row should return "2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00"
– Sami
Nov 21 '18 at 22:24
@Sami ohh, didn't pay attention while going to bed :), let me try little more, thanks.
– Barbaros Özhan
Nov 21 '18 at 22:27
@Sami ohh, didn't pay attention while going to bed :), let me try little more, thanks.
– Barbaros Özhan
Nov 21 '18 at 22:27
1
1
Yeah, it happen, but still we have the edit button there ;)
– Sami
Nov 21 '18 at 23:20
Yeah, it happen, but still we have the edit button there ;)
– Sami
Nov 21 '18 at 23:20
add a comment |
0
You can get your desired results as
WITH Dates AS
(
SELECT 1 RN,
@sd StartD,
CAST(DATEADD(Day, -1, @ed) AS DATE) EndD
UNION
SELECT 2,
CAST(DATEADD(Day, 1, @sd) AS DATE),
CAST(@ed AS DATE)
UNION
SELECT 3,
CAST(@ed AS DATE),
@ed
)
SELECT
CAST( DATEDIFF(YEAR, StartD, EndD) AS VARCHAR(10) )+ ' Years, '+
CAST( DATEDIFF(MONTH, StartD, EndD)%12 AS VARCHAR(10) )+ ' Months, '+
CAST( (DATEDIFF(DAY, StartD, EndD)%31)%12
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) )+ ' Days, '+
CAST( (DATEDIFF(HOUR, StartD, EndD)%60)%24
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) ) + ' Hours,'+
CAST( DATEDIFF(MINUTE, StartD, EndD)%60 AS VARCHAR(10) ) +' Minutes, '+
CAST( DATEDIFF(SECOND, StartD, EndD)%60 AS VARCHAR(10) ) +' Seconds' AS Results
FROM Dates;
Results:
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 0 Days, 0 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
Demo
Finally, as a note I think your results should be
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 1 Days, 1 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
if I'm right, then all what you need to do is removing the two (2) CASE expressions.
answered Nov 21 '18 at 23:07
SamiSami
8,94331241
add a comment |
0
You can get your desired results as
WITH Dates AS
(
SELECT 1 RN,
@sd StartD,
CAST(DATEADD(Day, -1, @ed) AS DATE) EndD
UNION
SELECT 2,
CAST(DATEADD(Day, 1, @sd) AS DATE),
CAST(@ed AS DATE)
UNION
SELECT 3,
CAST(@ed AS DATE),
@ed
)
SELECT
CAST( DATEDIFF(YEAR, StartD, EndD) AS VARCHAR(10) )+ ' Years, '+
CAST( DATEDIFF(MONTH, StartD, EndD)%12 AS VARCHAR(10) )+ ' Months, '+
CAST( (DATEDIFF(DAY, StartD, EndD)%31)%12
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) )+ ' Days, '+
CAST( (DATEDIFF(HOUR, StartD, EndD)%60)%24
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) ) + ' Hours,'+
CAST( DATEDIFF(MINUTE, StartD, EndD)%60 AS VARCHAR(10) ) +' Minutes, '+
CAST( DATEDIFF(SECOND, StartD, EndD)%60 AS VARCHAR(10) ) +' Seconds' AS Results
FROM Dates;
Results:
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 0 Days, 0 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
Demo
Finally, as a note I think your results should be
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 1 Days, 1 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
if I'm right, then all what you need to do is removing the two (2) CASE expressions.
answered Nov 21 '18 at 23:07
SamiSami
8,94331241
add a comment |
0
0
0
You can get your desired results as
WITH Dates AS
(
SELECT 1 RN,
@sd StartD,
CAST(DATEADD(Day, -1, @ed) AS DATE) EndD
UNION
SELECT 2,
CAST(DATEADD(Day, 1, @sd) AS DATE),
CAST(@ed AS DATE)
UNION
SELECT 3,
CAST(@ed AS DATE),
@ed
)
SELECT
CAST( DATEDIFF(YEAR, StartD, EndD) AS VARCHAR(10) )+ ' Years, '+
CAST( DATEDIFF(MONTH, StartD, EndD)%12 AS VARCHAR(10) )+ ' Months, '+
CAST( (DATEDIFF(DAY, StartD, EndD)%31)%12
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) )+ ' Days, '+
CAST( (DATEDIFF(HOUR, StartD, EndD)%60)%24
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) ) + ' Hours,'+
CAST( DATEDIFF(MINUTE, StartD, EndD)%60 AS VARCHAR(10) ) +' Minutes, '+
CAST( DATEDIFF(SECOND, StartD, EndD)%60 AS VARCHAR(10) ) +' Seconds' AS Results
FROM Dates;
Results:
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 0 Days, 0 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
Demo
Finally, as a note I think your results should be
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 1 Days, 1 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
if I'm right, then all what you need to do is removing the two (2) CASE expressions.
answered Nov 21 '18 at 23:07
SamiSami
8,94331241
You can get your desired results as
WITH Dates AS
(
SELECT 1 RN,
@sd StartD,
CAST(DATEADD(Day, -1, @ed) AS DATE) EndD
UNION
SELECT 2,
CAST(DATEADD(Day, 1, @sd) AS DATE),
CAST(@ed AS DATE)
UNION
SELECT 3,
CAST(@ed AS DATE),
@ed
)
SELECT
CAST( DATEDIFF(YEAR, StartD, EndD) AS VARCHAR(10) )+ ' Years, '+
CAST( DATEDIFF(MONTH, StartD, EndD)%12 AS VARCHAR(10) )+ ' Months, '+
CAST( (DATEDIFF(DAY, StartD, EndD)%31)%12
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) )+ ' Days, '+
CAST( (DATEDIFF(HOUR, StartD, EndD)%60)%24
- CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) ) + ' Hours,'+
CAST( DATEDIFF(MINUTE, StartD, EndD)%60 AS VARCHAR(10) ) +' Minutes, '+
CAST( DATEDIFF(SECOND, StartD, EndD)%60 AS VARCHAR(10) ) +' Seconds' AS Results
FROM Dates;
Results:
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 0 Days, 0 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
Demo
Finally, as a note I think your results should be
+----------------------------------------------------------+
| Results |
+----------------------------------------------------------+
| 0 Years, 0 Months, 1 Days, 1 Hours,20 Minutes, 0 Seconds |
| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds |
| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |
+----------------------------------------------------------+
if I'm right, then all what you need to do is removing the two (2) CASE expressions.
answered Nov 21 '18 at 23:07
SamiSami
8,94331241
answered Nov 21 '18 at 23:07
SamiSami
8,94331241
answered Nov 21 '18 at 23:07
SamiSami
8,94331241
answered Nov 21 '18 at 23:07
SamiSami
8,94331241
8,94331241
add a comment |
add a comment |
0
I believe you will want the ability to apply this to a variety of task durations. Here I have used a dynamic "tally table" which just a series of numbers from 0 to (in this case) 100, if you need a greater range amend the Tally cte by adding extra cross joins. If you already have a table of numbers, or your own preferred method for this substitute that for mine.
CREATE TABLE mytable(
ID Integer NOT NULL
,Start_Date datetime NOT NULL
,End_Date datetime NOT NULL
);
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (1,'2018-11-21 23:40:00','2018-11-23 02:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (2,'2018-11-23 13:40:00','2018-11-23 22:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (3,'2018-11-18 23:40:00','2018-11-23 02:40:00');
3 test cases, as given, + a very short duration + a longer duration:
;WITH
Digits AS (
SELECT 0 AS digit UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
)
, Tally AS (
SELECT [ones].digit
+ [tens].digit * 10
-- + [hundreds].digit * 100
AS number
FROM Digits [ones]
CROSS JOIN Digits [tens]
-- CROSS JOIN Digits [hundreds]
)
select
ID
, cast(ca2.minutes/60 as varchar) + ' hours '
+ cast(ca2.minutes % 60 as varchar) + ' minutes' as Duration
, format(on_date,'yyyy-MM-dd') on_date
, portion
, format(start_date,'yyyy-MM-dd hh:mm') start_date
, format(end_date,'yyyy-MM-dd hh:mm') end_date
from mytable t
inner join Tally on Tally.number <= datediff(dd,t.Start_Date,t.End_Date)
cross apply (
select dateadd(dd,tally.number,dateadd(dd,datediff(dd,0,t.start_date),0)) on_date
) ca
cross apply (
select
case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then datediff(minute,t.start_date,t.end_date)
when cast(t.end_date as date) = ca.on_date then datediff(minute,ca.on_date,t.end_date)
when ca.on_date between t.start_date and t.end_date then 24*60
when ca.on_date < t.start_date then datediff(minute,t.start_date,dateadd(dd,1,ca.on_date))
end minutes
, case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then 'is on'
when cast(t.end_date as date) = ca.on_date then 'end'
when ca.on_date between t.start_date and t.end_date then 'span'
when ca.on_date < t.start_date then 'start'
end portion
) ca2
I have used 2 apply operators, the first turns a number from the Tally into a date, and this is then re-used by the alias on_date in the second apply where we can calculate the duration. Note the second case expression portion is not essential, it's just there to help document the logic.
Result:
ID Duration on_date portion start_date end_date
---- ---- -------------------- ------------ --------- ------------------ ------------------
1 1 0 hours 20 minutes 2018-11-21 start 2018-11-21 11:40 2018-11-23 02:40
2 1 24 hours 0 minutes 2018-11-22 span 2018-11-21 11:40 2018-11-23 02:40
3 1 2 hours 40 minutes 2018-11-23 end 2018-11-21 11:40 2018-11-23 02:40
4 2 9 hours 0 minutes 2018-11-23 is on 2018-11-23 01:40 2018-11-23 10:40
5 3 0 hours 20 minutes 2018-11-18 start 2018-11-18 11:40 2018-11-23 02:40
6 3 24 hours 0 minutes 2018-11-19 span 2018-11-18 11:40 2018-11-23 02:40
7 3 24 hours 0 minutes 2018-11-20 span 2018-11-18 11:40 2018-11-23 02:40
8 3 24 hours 0 minutes 2018-11-21 span 2018-11-18 11:40 2018-11-23 02:40
9 3 24 hours 0 minutes 2018-11-22 span 2018-11-18 11:40 2018-11-23 02:40
10 3 2 hours 40 minutes 2018-11-23 end 2018-11-18 11:40 2018-11-23 02:40
also see: https://rextester.com/VPTI85082
answered Nov 21 '18 at 23:29
Used_By_AlreadyUsed_By_Already
23k22038
add a comment |
0
I believe you will want the ability to apply this to a variety of task durations. Here I have used a dynamic "tally table" which just a series of numbers from 0 to (in this case) 100, if you need a greater range amend the Tally cte by adding extra cross joins. If you already have a table of numbers, or your own preferred method for this substitute that for mine.
CREATE TABLE mytable(
ID Integer NOT NULL
,Start_Date datetime NOT NULL
,End_Date datetime NOT NULL
);
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (1,'2018-11-21 23:40:00','2018-11-23 02:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (2,'2018-11-23 13:40:00','2018-11-23 22:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (3,'2018-11-18 23:40:00','2018-11-23 02:40:00');
3 test cases, as given, + a very short duration + a longer duration:
;WITH
Digits AS (
SELECT 0 AS digit UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
)
, Tally AS (
SELECT [ones].digit
+ [tens].digit * 10
-- + [hundreds].digit * 100
AS number
FROM Digits [ones]
CROSS JOIN Digits [tens]
-- CROSS JOIN Digits [hundreds]
)
select
ID
, cast(ca2.minutes/60 as varchar) + ' hours '
+ cast(ca2.minutes % 60 as varchar) + ' minutes' as Duration
, format(on_date,'yyyy-MM-dd') on_date
, portion
, format(start_date,'yyyy-MM-dd hh:mm') start_date
, format(end_date,'yyyy-MM-dd hh:mm') end_date
from mytable t
inner join Tally on Tally.number <= datediff(dd,t.Start_Date,t.End_Date)
cross apply (
select dateadd(dd,tally.number,dateadd(dd,datediff(dd,0,t.start_date),0)) on_date
) ca
cross apply (
select
case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then datediff(minute,t.start_date,t.end_date)
when cast(t.end_date as date) = ca.on_date then datediff(minute,ca.on_date,t.end_date)
when ca.on_date between t.start_date and t.end_date then 24*60
when ca.on_date < t.start_date then datediff(minute,t.start_date,dateadd(dd,1,ca.on_date))
end minutes
, case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then 'is on'
when cast(t.end_date as date) = ca.on_date then 'end'
when ca.on_date between t.start_date and t.end_date then 'span'
when ca.on_date < t.start_date then 'start'
end portion
) ca2
I have used 2 apply operators, the first turns a number from the Tally into a date, and this is then re-used by the alias on_date in the second apply where we can calculate the duration. Note the second case expression portion is not essential, it's just there to help document the logic.
Result:
ID Duration on_date portion start_date end_date
---- ---- -------------------- ------------ --------- ------------------ ------------------
1 1 0 hours 20 minutes 2018-11-21 start 2018-11-21 11:40 2018-11-23 02:40
2 1 24 hours 0 minutes 2018-11-22 span 2018-11-21 11:40 2018-11-23 02:40
3 1 2 hours 40 minutes 2018-11-23 end 2018-11-21 11:40 2018-11-23 02:40
4 2 9 hours 0 minutes 2018-11-23 is on 2018-11-23 01:40 2018-11-23 10:40
5 3 0 hours 20 minutes 2018-11-18 start 2018-11-18 11:40 2018-11-23 02:40
6 3 24 hours 0 minutes 2018-11-19 span 2018-11-18 11:40 2018-11-23 02:40
7 3 24 hours 0 minutes 2018-11-20 span 2018-11-18 11:40 2018-11-23 02:40
8 3 24 hours 0 minutes 2018-11-21 span 2018-11-18 11:40 2018-11-23 02:40
9 3 24 hours 0 minutes 2018-11-22 span 2018-11-18 11:40 2018-11-23 02:40
10 3 2 hours 40 minutes 2018-11-23 end 2018-11-18 11:40 2018-11-23 02:40
also see: https://rextester.com/VPTI85082
answered Nov 21 '18 at 23:29
Used_By_AlreadyUsed_By_Already
23k22038
add a comment |
0
0
0
I believe you will want the ability to apply this to a variety of task durations. Here I have used a dynamic "tally table" which just a series of numbers from 0 to (in this case) 100, if you need a greater range amend the Tally cte by adding extra cross joins. If you already have a table of numbers, or your own preferred method for this substitute that for mine.
CREATE TABLE mytable(
ID Integer NOT NULL
,Start_Date datetime NOT NULL
,End_Date datetime NOT NULL
);
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (1,'2018-11-21 23:40:00','2018-11-23 02:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (2,'2018-11-23 13:40:00','2018-11-23 22:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (3,'2018-11-18 23:40:00','2018-11-23 02:40:00');
3 test cases, as given, + a very short duration + a longer duration:
;WITH
Digits AS (
SELECT 0 AS digit UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
)
, Tally AS (
SELECT [ones].digit
+ [tens].digit * 10
-- + [hundreds].digit * 100
AS number
FROM Digits [ones]
CROSS JOIN Digits [tens]
-- CROSS JOIN Digits [hundreds]
)
select
ID
, cast(ca2.minutes/60 as varchar) + ' hours '
+ cast(ca2.minutes % 60 as varchar) + ' minutes' as Duration
, format(on_date,'yyyy-MM-dd') on_date
, portion
, format(start_date,'yyyy-MM-dd hh:mm') start_date
, format(end_date,'yyyy-MM-dd hh:mm') end_date
from mytable t
inner join Tally on Tally.number <= datediff(dd,t.Start_Date,t.End_Date)
cross apply (
select dateadd(dd,tally.number,dateadd(dd,datediff(dd,0,t.start_date),0)) on_date
) ca
cross apply (
select
case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then datediff(minute,t.start_date,t.end_date)
when cast(t.end_date as date) = ca.on_date then datediff(minute,ca.on_date,t.end_date)
when ca.on_date between t.start_date and t.end_date then 24*60
when ca.on_date < t.start_date then datediff(minute,t.start_date,dateadd(dd,1,ca.on_date))
end minutes
, case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then 'is on'
when cast(t.end_date as date) = ca.on_date then 'end'
when ca.on_date between t.start_date and t.end_date then 'span'
when ca.on_date < t.start_date then 'start'
end portion
) ca2
I have used 2 apply operators, the first turns a number from the Tally into a date, and this is then re-used by the alias on_date in the second apply where we can calculate the duration. Note the second case expression portion is not essential, it's just there to help document the logic.
Result:
ID Duration on_date portion start_date end_date
---- ---- -------------------- ------------ --------- ------------------ ------------------
1 1 0 hours 20 minutes 2018-11-21 start 2018-11-21 11:40 2018-11-23 02:40
2 1 24 hours 0 minutes 2018-11-22 span 2018-11-21 11:40 2018-11-23 02:40
3 1 2 hours 40 minutes 2018-11-23 end 2018-11-21 11:40 2018-11-23 02:40
4 2 9 hours 0 minutes 2018-11-23 is on 2018-11-23 01:40 2018-11-23 10:40
5 3 0 hours 20 minutes 2018-11-18 start 2018-11-18 11:40 2018-11-23 02:40
6 3 24 hours 0 minutes 2018-11-19 span 2018-11-18 11:40 2018-11-23 02:40
7 3 24 hours 0 minutes 2018-11-20 span 2018-11-18 11:40 2018-11-23 02:40
8 3 24 hours 0 minutes 2018-11-21 span 2018-11-18 11:40 2018-11-23 02:40
9 3 24 hours 0 minutes 2018-11-22 span 2018-11-18 11:40 2018-11-23 02:40
10 3 2 hours 40 minutes 2018-11-23 end 2018-11-18 11:40 2018-11-23 02:40
also see: https://rextester.com/VPTI85082
answered Nov 21 '18 at 23:29
Used_By_AlreadyUsed_By_Already
23k22038
I believe you will want the ability to apply this to a variety of task durations. Here I have used a dynamic "tally table" which just a series of numbers from 0 to (in this case) 100, if you need a greater range amend the Tally cte by adding extra cross joins. If you already have a table of numbers, or your own preferred method for this substitute that for mine.
CREATE TABLE mytable(
ID Integer NOT NULL
,Start_Date datetime NOT NULL
,End_Date datetime NOT NULL
);
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (1,'2018-11-21 23:40:00','2018-11-23 02:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (2,'2018-11-23 13:40:00','2018-11-23 22:40:00');
INSERT INTO mytable(id,Start_Date,End_Date) VALUES (3,'2018-11-18 23:40:00','2018-11-23 02:40:00');
3 test cases, as given, + a very short duration + a longer duration:
;WITH
Digits AS (
SELECT 0 AS digit UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
)
, Tally AS (
SELECT [ones].digit
+ [tens].digit * 10
-- + [hundreds].digit * 100
AS number
FROM Digits [ones]
CROSS JOIN Digits [tens]
-- CROSS JOIN Digits [hundreds]
)
select
ID
, cast(ca2.minutes/60 as varchar) + ' hours '
+ cast(ca2.minutes % 60 as varchar) + ' minutes' as Duration
, format(on_date,'yyyy-MM-dd') on_date
, portion
, format(start_date,'yyyy-MM-dd hh:mm') start_date
, format(end_date,'yyyy-MM-dd hh:mm') end_date
from mytable t
inner join Tally on Tally.number <= datediff(dd,t.Start_Date,t.End_Date)
cross apply (
select dateadd(dd,tally.number,dateadd(dd,datediff(dd,0,t.start_date),0)) on_date
) ca
cross apply (
select
case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then datediff(minute,t.start_date,t.end_date)
when cast(t.end_date as date) = ca.on_date then datediff(minute,ca.on_date,t.end_date)
when ca.on_date between t.start_date and t.end_date then 24*60
when ca.on_date < t.start_date then datediff(minute,t.start_date,dateadd(dd,1,ca.on_date))
end minutes
, case
when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then 'is on'
when cast(t.end_date as date) = ca.on_date then 'end'
when ca.on_date between t.start_date and t.end_date then 'span'
when ca.on_date < t.start_date then 'start'
end portion
) ca2
I have used 2 apply operators, the first turns a number from the Tally into a date, and this is then re-used by the alias on_date in the second apply where we can calculate the duration. Note the second case expression portion is not essential, it's just there to help document the logic.
Result:
ID Duration on_date portion start_date end_date
---- ---- -------------------- ------------ --------- ------------------ ------------------
1 1 0 hours 20 minutes 2018-11-21 start 2018-11-21 11:40 2018-11-23 02:40
2 1 24 hours 0 minutes 2018-11-22 span 2018-11-21 11:40 2018-11-23 02:40
3 1 2 hours 40 minutes 2018-11-23 end 2018-11-21 11:40 2018-11-23 02:40
4 2 9 hours 0 minutes 2018-11-23 is on 2018-11-23 01:40 2018-11-23 10:40
5 3 0 hours 20 minutes 2018-11-18 start 2018-11-18 11:40 2018-11-23 02:40
6 3 24 hours 0 minutes 2018-11-19 span 2018-11-18 11:40 2018-11-23 02:40
7 3 24 hours 0 minutes 2018-11-20 span 2018-11-18 11:40 2018-11-23 02:40
8 3 24 hours 0 minutes 2018-11-21 span 2018-11-18 11:40 2018-11-23 02:40
9 3 24 hours 0 minutes 2018-11-22 span 2018-11-18 11:40 2018-11-23 02:40
10 3 2 hours 40 minutes 2018-11-23 end 2018-11-18 11:40 2018-11-23 02:40
also see: https://rextester.com/VPTI85082
answered Nov 21 '18 at 23:29
Used_By_AlreadyUsed_By_Already
23k22038
edited Nov 21 '18 at 23:49
answered Nov 21 '18 at 23:29
Used_By_AlreadyUsed_By_Already
23k22038
answered Nov 21 '18 at 23:29
Used_By_AlreadyUsed_By_Already
23k22038
answered Nov 21 '18 at 23:29
Used_By_AlreadyUsed_By_Already
23k22038
23k22038
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420014%2freturn-time-to-end-of-next-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
The task is not hard but probably you need to explain more like what is the logic? and why it can't be done?
– ViKiNG
Nov 21 '18 at 20:34
@ViKiNG Because I have no idea how to remove time (set 00:00:00 if to say correctly) for next days
– Valentyn
Nov 21 '18 at 20:44
2
@HimanshuAhuja Did you see Oracle tag there?
– Sami
Nov 21 '18 at 21:16
1
@HimanshuAhuja From where did you get rownum?
– Sami
Nov 21 '18 at 21:33
1
@HimanshuAhuja Simply, that won't return 3 rows as OP wanted, and it won't work anyway.
– Sami
Nov 21 '18 at 21:45