asked on

DateDiff in SQL Query using Table/Field and CurrentDate

I need to subtract a field in my database that is a datetime field from the current date at the time the report is run.
Then I need the answer displayed in days, hours and minutes as shown here:
11d 21h 56m

This is a one time report so I don't need to use declared variables, etc., just something simple will do.
I have seen lots of examples using variables but do not know enough sequel to turn those into a simple query.

Can someone help?

My table/field is op.order_date.

Mike McCracken

Not sure how to do it in SQL.

Here is a formula to do it.

WhilePrintingRecords;
Local NumberVar myMinutes;
Local NumberVar  myHours;
Local NumberVar myDays;

myMinutes:= DateDiff('n',{op.order_date},CurrentDateTime);
myDays := myMinutes \ 1440;
myMinutes := myMinutes  MOD 1440;
myHours := myMinutes  \ 60;
myMinutes  := myMinutes  MOD 60

CStr(myDays,0) &  'd ' & CStr(myHours,0) & 'h ' & CStr(myMinutes,0) & 'm'

Open in new window

mlmcc

Brendt Hess

This is sample code that works for me. You may need to modify it to integrate it into your SQL.

SELECT 
	(mins / 60 - ((mins / 60) % 24))/24 as days,
	(Mins / 60) % 24 as hrs,
	hrMins as mins
FROM (
	SELECT mins%60 as hrMins,
		mins - (mins%60) as mins
	FROM (
		SELECT datediff(minute, op.order_date, current_timestamp) as mins
		) as minStep
	) midStep

Open in new window

Becky Edwards

ASKER

mlmcc: When I try to place the ode above into a formula in Crystal, it comes back with an error message that says the last line is not part of a formula.

bhess1:
I have no idea how to incorporate those subqueries into my sequel query. My query is attached. Perhaps you can give me some idea?
ECG-Orders.sql

Becky Edwards

ASKER

This is actually what worked. The comments were helpful.

select distinct right('0' + cast(datediff(DAY, op.ORDERING_DATE, current_timestamp) as varchar),2)

+ ':' +

right('0' + cast(datediff(hh, op.ORDERING_DATE, current_timestamp) as varchar),2)

+ ':' +

right('0' + cast(datediff(mi, op.ORDERING_DATE, current_timestamp) % 60 as varchar),2) as duration

FROM Order_Proc op

WHERE op.ORDERING_DATE = '08/20/2015'

Becky Edwards

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for bjrhart's comment #a40945058
Assisted answer: 100 points for mlmcc's comment #a40944930
Assisted answer: 200 points for bhess1's comment #a40944994

for the following reason:

Neither comment was exactly what I needed, but your comments helped me to find the correct solution. Thank you.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Oh, and if you have 2012 and .Net framework installed (and who doesn't these days)

then use the FORMAT command (finally in T-SQL)

SELECT FORMAT( (getdate() - datefld), 'dd\d HH\h mm\m', 'en-US' ) AS DayHourMin
from Your_Date_Data

Open in new window

Mike McCracken

Missing a ;

WhilePrintingRecords;
Local NumberVar myMinutes;
Local NumberVar  myHours;
Local NumberVar myDays;

myMinutes:= DateDiff('n',{op.order_date},CurrentDateTime);
myDays := myMinutes \ 1440;
myMinutes := myMinutes  MOD 1440;
myHours := myMinutes  \ 60;
myMinutes  := myMinutes  MOD 60;

CStr(myDays,0) &  'd ' & CStr(myHours,0) & 'h ' & CStr(myMinutes,0) & 'm'

Open in new window

PortletPaul

but the formula used is not correct....

It can produce weird results like 25 hours (an actual result shown below)

|                          | duration |
|--------------------------|----------|
| August, 25 2015 05:58:44 | 05:25:58 |

Open in new window

  CREATE TABLE Order_Proc 
        ([ORDERING_DATE] datetime)
    ;
        
    INSERT INTO Order_Proc 
        ([ORDERING_DATE])
    VALUES
        ('2015-08-20 00:00:00')
    ;
    
**Query 1**:

    select
    current_timestamp
    , right('0' + cast(datediff(DAY, op.ORDERING_DATE, current_timestamp) as varchar),2)
    
    + ':' +
    
    right('0' + cast(datediff(hh, op.ORDERING_DATE, current_timestamp) as varchar),2)
    
    + ':' +
    
    right('0' + cast(datediff(mi, op.ORDERING_DATE, current_timestamp) % 60 as varchar),2) as duration
    
    FROM Order_Proc op
    
    WHERE op.ORDERING_DATE = '08/20/2015'

**[Results][2]**:
    |                          | duration |
    |--------------------------|----------|
    | August, 25 2015 05:58:44 | 05:25:58 |

  [1]: http://sqlfiddle.com/#!3/cc13c/2

Open in new window

Becky Edwards

ASKER

This one worked perfectly! Thank You.

Becky Edwards

ASKER

And thank you mlmcc I am also able to use yours now in a crystal report!

Becky Edwards

ASKER

And thank you Paul for letting me know my solution would not be correct to use.....