July 7, 2015 at 3:40 am
I am getting better each day, my rusty SQL head is getting there!
I am going put in the whole query but my question is on changing a datediff minutes amount into DD:HH:MM.
Query is:
select c.APPLICANT_ID as [Applicant ID], aetc.EVENT_TYPE as [Event Type],
cast(aetr.CREATE_DATE as date) as [Registration Date],
cast(aetc.CREATE_DATE as date) as [C Creation Date],
datediff(mi,cast(aetr.CREATE_DATE as datetime),cast(aetc.CREATE_DATE as datetime)) as [time diff],
cast(c.CREATE_DATE as date) as [c create date], app.APPLICATION_ID as [Application ID]
from c C
join AET_REGISTRATION AETR on c.APPLICANT_ID = AETR.APPLICANT_ID
join AET_C AETC on c.APPLICANT_ID = aetc.APPLICANT_ID
left join application app on c.APPLICANT_ID = app.APPLICANT_ID
where C.CREATE_DATE >=DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0))
and aetc.EVENT_TYPE in ('new_cv_dist', 'new_cv_hide')
and c.CREATE_DATE < aetc.CREATE_DATE
and app.application_id is not null
order by c.CREATE_DATE asc;
The bit I know I need to change is datediff(mi,cast(aetr.CREATE_DATE as datetime),cast(aetc.CREATE_DATE as datetime)) as [time diff], I want this as dd:hh:mm rather than just minutes.
Currently this field is just minutes so a figure such as 20, depending on the time difference between the 2 dates.
I have tried the convert statement convert (char(5) ...........,108) within this but that's not working and I have used floor before to do this type of thing but not sure where that should go.
Any help is appreciated.
July 7, 2015 at 3:54 am
You might consider having your presentation layer doing the formatting. If that's not practical, use the modulo (%) operator, which gives the remainder when one number is divided by another. The remainder when the total number of minutes is divided by 60 gives the minute component; you can get the hours and days in a similar way.
John
July 7, 2015 at 4:02 am
I'm with John on this one. What datatype is aetr.CREATE_DATE ?
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
July 7, 2015 at 4:14 am
Hi John,
Thanks for your reply, you might have lost me on that one could you explain that further?
Chris the datatype is datetime for that field if that helps.
July 7, 2015 at 4:28 am
Not sure what you don't understand. Say you have 17562 minutes. Then the minute portion of that period is 17562 % 60, or 42. Now subtract that 42 from the original number to give the period not including the minute portion: 17562 - 42 = 17520 minutes. Divide that by 60 to get the number of hours: 17520 / 60 = 292 hours. To get the hour portion, use the modulo again: 292 % 24 = 4. Subtract that from the 292 to get the number hours not including the remainder: 292 - 4 = 288. Then divide by 24 to get the number of days: 288 / 24 = 12. So 17562 is 12:04:42 in dd:hh:mm.
John
July 7, 2015 at 5:32 am
karen.blake (7/7/2015)
Hi John,Thanks for your reply, you might have lost me on that one could you explain that further?
Chris the datatype is datetime for that field if that helps.
Thanks. Couple of simplifications here:
select
c.APPLICANT_ID as [Applicant ID],
aetc.EVENT_TYPE as [Event Type],
cast(aetr.CREATE_DATE as date) as [Registration Date],
cast(aetc.CREATE_DATE as date) as [C Creation Date],
datediff(mi, aetr.CREATE_DATE, aetc.CREATE_DATE) as [time diff],
cast(c.CREATE_DATE as date) as [c create date],
app.APPLICATION_ID as [Application ID]
from c C
join AET_REGISTRATION AETR
on c.APPLICANT_ID = AETR.APPLICANT_ID
join AET_C AETC
on c.APPLICANT_ID = aetc.APPLICANT_ID
left join [application] app
on c.APPLICANT_ID = app.APPLICANT_ID
where C.CREATE_DATE >= DATEADD(month,-12,CAST(GETDATE() AS DATE)) -- simpler than original
and aetc.EVENT_TYPE in ('new_cv_dist', 'new_cv_hide')
and c.CREATE_DATE < aetc.CREATE_DATE
and app.application_id is not null
order by c.CREATE_DATE asc;
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
July 7, 2015 at 6:38 am
Hi John, I get the arithmetic, I was unsure how to construct the query to get the values as this. Apologies if I am being dim, it seems straightforward in my head but clearly I can't rattle the bits out my queries need.
Chris, oddly I had the query like that to start with then changed it. My bad.
July 7, 2015 at 7:12 am
Start from the inside and work your way out:
DECLARE
@minutes int = 17562
,@minportion int
,@hours int
,@hourportion int
,@days int
,@ddhhmm char(8)
SET @minportion = @minutes%60
SET @hours = (@minutes - @minportion)/60
SET @hourportion = @hours%24
SET @days = (@hours - @hourportion)/24
SET @ddhhmm = CAST(@days AS char(2))+':'+CAST(@hourportion AS char(2))+':'+CAST(@minportion AS char(2))
SELECT @ddhhmm
Put it all together:
SELECT CAST(((@minutes - ((@minutes - @minutes%60)/60)%24)/60 - ((@minutes - @minutes%60)/60)%24)/24 AS char(2))+':'+CAST(((@minutes - @minutes%60)/60)%24 AS char(2))+':'+CAST(@minutes%60 AS char(2))
You'd want to make it a little bit more sophisticated than I've done, perhaps by padding with leading zeros, or by allowing for numbers of days greater than 99.
John
July 7, 2015 at 9:27 am
This is another formula to get there.
WITH TestData AS(
SELECT GETDATE() - RAND(CHECKSUM(NEWID())) * 7 AS date1, GETDATE() + RAND(CHECKSUM(NEWID())) * 7 AS date2
FROM sys.all_columns
)
select date1, date2,
CAST( DATEPART(DAYOFYEAR, DATEADD( minute, DATEDIFF( minute, date1, date2), 0)) AS varchar(3)) + ':'
+ CONVERT( char(5), DATEADD( minute, DATEDIFF( minute, date1, date2), 0), 108)
from TestData;
July 7, 2015 at 9:50 am
select c.APPLICANT_ID as [Applicant ID], aetc.EVENT_TYPE as [Event Type],
cast(aetr.CREATE_DATE as date) as [Registration Date],
cast(aetc.CREATE_DATE as date) as [C Creation Date],
right('0' + cast(minutes_diff / 1440 as varchar(2)), 2) + ':' + --dd:
right('0' + cast(minutes_diff % 1440 / 60 as varchar(2)), 2) + ':' + --hh:
right('0' + cast(minutes_diff % 60 as varchar(2)), 2) /*mm*/ as [Time Diff],
cast(c.CREATE_DATE as date) as [c create date], app.APPLICATION_ID as [Application ID]
from c C
join AET_REGISTRATION AETR on c.APPLICANT_ID = AETR.APPLICANT_ID
join AET_C AETC on c.APPLICANT_ID = aetc.APPLICANT_ID
left join application app on c.APPLICANT_ID = app.APPLICANT_ID
cross apply (
select datediff(mi,cast(aetr.CREATE_DATE as datetime),cast(aetc.CREATE_DATE as datetime)) as minutes_diff
) as assign_alias_names
where C.CREATE_DATE >=DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0))
and aetc.EVENT_TYPE in ('new_cv_dist', 'new_cv_hide')
and c.CREATE_DATE < aetc.CREATE_DATE
and app.application_id is not null
order by c.CREATE_DATE asc;
Sample minutes data and resulting output:
select
minutes_diff,
right('0' + cast(minutes_diff / 1440 as varchar(2)), 2) + ':' + --dd:
right('0' + cast(minutes_diff % 1440 / 60 as varchar(2)), 2) + ':' + --hh:
right('0' + cast(minutes_diff % 60 as varchar(2)), 2) --mm
as [time diff]
from (
select 20 as minutes_diff union all
select 2314 union all
select 32551
) as test_data
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 7, 2015 at 3:25 pm
As John pointed out earlier, this really is a display function and may best be handled in the from end app or in reporting software.
If you'd like to take that rout, this is a function I've used in SSRS in the past.
It uses seconds instead of minutes but it would be easy enough to make the necessary adjustments...
Function DHMS2(NumberOfSeconds as Long) as String
Dim NumberOfMinutes as Long
Dim NumberOfHours as Long
Dim NumberOfDays as Long
NumberOfDays = NumberOfSeconds \ 86400
NumberOfSeconds = NumberOfSeconds Mod 86400
NumberOfHours = NumberOfSeconds \ 3600
NumberOfSeconds = NumberOfSeconds Mod 3600
NumberOfMinutes = NumberOfSeconds \ 60
NumberOfSeconds = NumberOfSeconds Mod 60
DHMS2 = IIF(NumberofDays<10,"0" & NumberOfDays, NumberOfDays) & ":"
DHMS2 = DHMS2 &IIF(NumberOfHours<10,"0" & NumberOfHours, NumberOfHours) & ":"
DHMS2 = DHMS2 &IIF(NumberOfMinutes<10,"0" & NumberOfMinutes, NumberOfMinutes) & ":"
DHMS2 = DHMS2 &IIF(NumberOfSeconds<10,"0" & NumberOfSeconds,NumberOfSeconds)
End Function
July 7, 2015 at 10:22 pm
While I certainly find time calculus fun - I usually find it a LOt more readable to simply get there using date substraction, and pulling out the parts I need, as in something like:
declare @startdate datetime;
declare @enddate datetime;
declare @diff datetime;
set @startdate='2012-05-01 15:12:00.230';
set @enddate='2013-12-11 21:48:00.530';
select @diff=@enddate-@startdate;
select cast(datediff(day,0,@diff) as varchar(20))+':'
+cast(datepart(hour,@diff) as varchar(20))+':'
+cast(datepart(MINUTE,@diff) as varchar(20))+':'
+cast(datepart(SECOND,@diff) as varchar(20))
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 9, 2015 at 1:23 am
Sorry for my delay, I had yesterday off. Thanks for this, I will look through all the suggestions this morning and then let you know which one(s) I incorporated. Really appreciate the help!
July 9, 2015 at 4:22 am
Hi ,
following query meets your requirements if there is any wrong correct me .
declare @StartDate datetime, @EndDate datetime
select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'
select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]
select convert(varchar(5),DateDiff(s, '10/01/2012 08:40:18.000','10/04/2012 09:52:48.000')/3600)---FOR HOURS
select convert(varchar(5),DateDiff(s, '10/01/2012 08:40:18.000','10/04/2012 09:52:48.000')%3600/60)---FOR Minutes
select convert(varchar(5),DateDiff(s, '10/01/2012 08:40:18.000','10/04/2012 09:52:48.000')%60)-----For Secs
declare @StartDate datetime, @EndDate datetime, @Day varchar(5),@Month varchar(20)
select @StartDate='10/01/2012 08:40:18.000', @EndDate='10/04/2012 09:52:48.000'
select @day =CONVERT(varchar(5),DateDiff(d,@StartDate,@EndDate))
IF @day>30
BEGIN
select @Month=CONVERT(varchar(5),DateDiff(d,@StartDate,@EndDate)%30)
END
+':'+
CONVERT(varchar(5), DateDiff(s,@StartDate,@EndDate)/3600)+':'+
CONVERT(varchar(5),DateDiff(s,@StartDate,@EndDate)%3600/60)+':'+
CONVERT(varchar(5),DateDiff(s,@StartDate,@EndDate)%60%60) AS "HH:MM:SS"
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply