Converting minutes or seconds to DD:HH:MM:SS 1

[Jcfx]

I have a crystal report that I am moving to reporting services that flags deliveries that are running late.

In crystal I used formula to convert seconds to the format I need, in the new report they would rather I used minutes as my base, either way I am a bit lost as doing date formula calculations in Reporting services is a nightmare So I am trying to do it sql side.

Old Crystal Formulas:

Datetimevar MyDate;

--Time Zone 
if {dc.cty_GMTDelta} = 4 then
MyDate:=dateadd("h",-1,{s.stp_arrivaldate})
else
if {dc.cty_GMTDelta} = 6 then
MyDate:=dateadd("h",1,{s.stp_arrivaldate})
else
if {dc.cty_GMTDelta} = 7 then
MyDate:=dateadd("h",2,{s.stp_arrivaldate})
else
if {dc.cty_GMTDelta} = 8 then
MyDate:=dateadd("h",3,{s.stp_arrivaldate})
else
MyDate := {s.stp_arrivaldate}

--Conversion
evaluateafter({@MyDate});
Datetimevar MyDate;
numbervar tsecs := datediff("s",MyDate,currentdatetime);
numbervar ndays := truncate(tsecs/86400);
tsecs := remainder(tsecs,86400); 
numbervar nhours := truncate(tsecs/3600); 
tsecs := remainder(tsecs,3600);
numbervar nmin := truncate(tsecs/60); 
tsecs := remainder(tsecs,60);

 

if nhours-24<=0 then totext(ndays,0)+"D: "+totext(nhours,0)+"H: "+ totext(nmin,0)+
"M: "

How can I do something similar as above useing sql as part of my stored procedure?

select
 o.ord_hdrnumber,
 o.ord_refnum,
 o.ord_startdate,
 s.stp_arrivaldate,
 s.stp_schdtlatest,
 s.stp_city,
 oc.cty_nmstct as Origin,
dc.cty_nmstct as Destination,
--got the minutes but need conversion
datediff(mi,s.stp_schdtlatest,stp_arrivaldate)as MinLate
--end conversion
from orderheader o,stops s, city oc,city dc
where o.ord_hdrnumber=s.ord_hdrnumber
and o.ord_status = 'CMP'
and oc.cty_code=o.ord_origincity and dc.cty_code = o.ord_destcity
and s.stp_city = dc.cty_code
order by stp_arrivaldate

I can probably wiggle out the time zone part of it, if someone can help me with the conversion side.

Thanks for any assistance

Julie
CRXI CE10 / RS2005 Sql DB

 

[jbenson001]

can you post some sample data and your required output?

 

[Jcfx]

Thanks for the reply, sorry for the delay.

Using data where currentdatetime is 10/2/06 8:28:23 the following sample data

stp_arrivaldate          Gm_delta   Lateby<expected output>
10-01-06 18:00:00          5           0d:14h:33M
09-30-2006 11:30:00        8           1d:20h:3M
09-29-2006 20:00:00        5           2d:12h:33m
10-02-2006 08:00:00        5            0d:0h:33m
10-01-2006 15:00:00        5            0d:17h:33m
09-27-2006 00:09:00        5            5d:8h:24m

Thanks again





Julie
CRXI CE10 / RS2005 Sql DB

 

[gmmastros]

This is ugly, but it should work. There's a trick here that needs a little explanation. DateDiff will return the elapsed time based on whatever units you give. The problem is that it returns an integer and will evaluate to a 1 even if it *really* shouldn't. Like this...

[tt][blue]Set DateFormat YMD
Select DateDiff(Day, '2006-10-01 23:59', '2006-10-02 00:01')[/blue][/tt]

These 2 dates are 2 minutes apart, but DateDiff returns 1 day because it spans midnight.

So... the trick is to check the datediff of 1 unit lower and then divide. To get the days, do datediff on hours and divide by 24. To get hours, do datediff on minutes and divide by 60, etc...

I put together a table variable where I could store your test values. You can copy/paste all the code below to a Query Analyzer window and run it to verify the results. You'll need to modify this a little for your situation, but this should be a good push in the right direction.

Declare @Temp Table (ArrivalDate DateTime)

Set DateFormat MDY
insert Into @Temp Values('10-01-2006 18:00:00')
insert Into @Temp Values('09-30-2006 11:30:00')
insert Into @Temp Values('09-29-2006 20:00:00')
insert Into @Temp Values('10-02-2006 08:00:00')
insert Into @Temp Values('10-01-2006 15:00:00')
insert Into @Temp Values('09-27-2006 00:09:00')

Select 	*,
		DateDiff(Hour, ArrivalDate, GetDate()) / 24 as Days,
		DateDiff(Minute, DateAdd(Day, DateDiff(Hour, ArrivalDate, GetDate()) / 24, ArrivalDate), GetDate()) / 60 As Hours,
		DateDiff(Second, DateAdd(Hour, DateDiff(Minute, DateAdd(Day, DateDiff(Hour, ArrivalDate, GetDate()) / 24, ArrivalDate), GetDate()) / 60, DateAdd(Day, DateDiff(Hour, ArrivalDate, GetDate()) / 24, ArrivalDate)), GetDate())/60 As Seconds
From 	@Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jcfx]

Thanks so much, I'll run with this and see where it leads me.

Julie
CRXI CE10 / RS2005 Sql DB