Calculate Time Elapse (days, hours, minutes)

I agree with Rob, but wanted to add that you could use a derived table to make the calculations easier.

The basics is what Rob showed with converting the integer to character data then to date using the ISO standard format 112.  Since it converts to date from ISO string, this also works as a shorthand.

dateadd(mi, (ppadtm/100*60)+(ppadtm%100), convert(varchar,ppaddt))

convert(varchar, ppaddt) is same as Rob's convert(char(8), ppaddt) ==> '20090701'

dateadd takes three parameters with first being the interval (e.g., mi or minute); the adjustment amount; then original date.  Using the fact that the third parameter takes a date, the ISO string gets converted automatically to a date eliminating the convert(datetime, {value}, 112) showed earlier; however, that would be more explicit approach.  Then to keep things as tightly coded as possible, we use the same statement to add in number of minutes decoded as the hours * 60 and minutes from military time as Rob showed.

Hope this helps.

Regards,

--isa

select datediff(dd, admit, discharge) lsod,
datediff(hh, admit, discharge) lsoh,
datediff(mi, admit, discharge) lsom
from (
select dateadd(mi, (ppadtm/100*60)+(ppadtm%100), convert(varchar,ppaddt)) admit, 
dateadd(mi, (ppdstm/100*60)+(ppdstm%100), convert(varchar,ppdsdt)) discharge
from your_table
) t

                                              

1:
2:
3:
4:
5:
6:
7:
8:

Forgot the reference to CAST/CONVERT:
http://msdn.microsoft.com/en-us/library/ms187928.aspx

Kevin (--isa),

I don't agree with:

convert(varchar, ppaddt) is same as Rob's convert(char(8), ppaddt) ==> '20090701'

You're much better off using char(8), as varchar without a length will likely soon be deprecated.

And as I feel like epicazo wants something that is very much reusable (ie, why he wants a UDF rather than just a query), I figured that using computed columns (therefore needing to refer to original fields, no derived tables allowed) would be a useful option.

Rob

Rob, I was trying to credit your solution.  I apologize for miscommunication.  I should have just typed the char(8), just took the lazy approach as I didn't want epicazo to miss the point that you already offered nice examples of the syntax -- my contribution was the derived table -- that is what I focused on.  My comments comparing to your was to just give you credit if that makes sense.

Here you go sir:

select datediff(dd, admit, discharge) lsod,
datediff(hh, admit, discharge) lsoh,
datediff(mi, admit, discharge) lsom
from (
select dateadd(mi, (ppadtm/100*60)+(ppadtm%100), convert(char(8),ppaddt)) admit, 
dateadd(mi, (ppdstm/100*60)+(ppdstm%100), convert(char(8),ppdsdt)) discharge
from your_table
) t

                                              

1:
2:
3:
4:
5:
6:
7:
8:

To the point of the computed columns, I would think these would work also:
dateadd(mi, (ppadtm/100*60)+(ppadtm%100), convert(char(8),ppaddt)) ==> admit
dateadd(mi, (ppdstm/100*60)+(ppdstm%100), convert(char(8),ppdsdt)) ==> discharge

I offered the derived table as I was supporting your point that use of UDF is probably inefficient, but my point is instead of making 3 computed columns for the different measures with repeated hashing of the dates we make 2 computed columns with the dates and take advantage of the standard datediff (date functions) when querying. ;)

I am sorry for the ingorance in my part, but what is a computed column?
Is it a computed column in a view or is it part of the actual table?

ERROR: syntax error converting datetime from character string
DATEDIFF(day, CONVERT (datetime, CONVERT (char(8), PPADDT), 112), CONVERT (datetime, CONVERT (char(8), PPDSDT), 112)) + 1

ERROR: Data Type expression error
dateadd(mi, (ppadtm/100*60)+(ppadtm%100), convert(char(8),ppaddt))



ERROR: Data Type expression error
datediff(day, convert(datetime,convert(char(8),PPADDT),112), convert(datetime,convert(char(8),PPDSDT),112)) * 24 * 60 + (PPDSTM / 100) * 60 + (PPDSTM % 100) - (PPDSTM / 100) * 60 - (PPADTM / 100)

ERROR: syntax error converting datetime from character string
(DATEDIFF(day, CONVERT (datetime, CONVERT (char(8), PPADDT), 112), CONVERT (datetime, CONVERT (char(8), PPDSDT), 112)) * 24 + PPDSTM / 100) - PPADTM / 100

Sounds to me as if your data is not all YYYYMMDD.

And, a computed column is a column you add to the table that you're loading with your AS400 data. You can read more in Books Online.

Rob

AS400 dates some times come out as integers like:

1090701 which is 07/01/2009

Is this what your data looks like?

If so, then instead of this:
dateadd(mi, (ppadtm/100*60)+(ppadtm%100), convert(char(8),ppaddt))

You would have to do this:
dateadd(mi, (ppadtm/100*60)+(ppadtm%100), convert(char(8),19000000+ppaddt))

Best regards,
Kevin

Sorry for the delay; I was caught up in another project.   My data looks like this.... yyyymmdd  but notice the time not always four digit..

PPADDT      PPADTM      PPDSDT      PPDSTM
20090701      2108      20090701      2225
20090702      653      20090702      653
20090701      819      20090701      1155
20090701      2013      20090702      105
20090701      2240      20090702      220
20090701      2251      20090702      230
20090701      1829      20090702      1729

The time doesn't need the leading zeros (better to not have them). So, a minute past midnight would just be 1.

But are PPADDT and PPDSDT ALWAYS proper date numbers?

Rob

YES always YYYYMMDD

Ok... but this error:

ERROR: syntax error converting datetime from character string
(DATEDIFF(day, CONVERT (datetime, CONVERT (char(8), PPADDT), 112), CONVERT (datetime, CONVERT (char(8), PPDSDT), 112)) * 24 + PPDSTM / 100) - PPADTM / 100

Would suggest that you have some records that don't fit. Are any NULL or empty?

Rob

(DATEDIFF(day, CONVERT (datetime, CONVERT (char(8), PPADDT), 112), CONVERT (datetime, CONVERT (char(8), PPDSDT), 112)) * 24 + PPDSTM / '100)') - PPADTM / 100   .... gives me an error "Error converting data type varchar to numeric"

There doesn't appear to be any blanks in calcated fields.

my bad, yes I still get the ERROR: syntax error converting datetime from character string

I just confirmed that some rows in my PPDSDT & PPDSTM column contain ZERO (0).

Do you want those values included as some default date OR shown as NULL?

The result should be null -- thank you so much

Something like this may work.  Can apply it to any of the computed column formulas you were using either from Rob's or mine.  Just showing with my 'admit' column formula.

(case when nullif([ppaddt],(0)) IS NOT NULL then dateadd(minute,([ppadtm]/(100))*(60)+[ppadtm]%(100),CONVERT([char](8),[ppaddt],0))  end)

It utilizes NULLIF to return NULL if date field value is 0.  This will now allow us to compare both NULL and 0 in one statement.  A better way to account for other bad data is to do something like this:

(case when coalesce([ppaddt],0) >= 17530101 then dateadd(minute,([ppadtm]/(100))*(60)+[ppadtm]%(100),CONVERT([char](8),[ppaddt],0))  end)

This uses COALESCE or ISNULL to change NULL values to 0 and then ensures that whether value was NULL or a malformed date it is at least greater than 17530101 which represents 1753-01-01 (the minimum allowed date for the datetime data type in SQL).

Kevin

OPTION1 (nullif): error
Server: Msg 206, Level 16, State 2, Procedure VW_mytest3, Line 5
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Procedure VW_mytest3, Line 5
Operand data type numeric is invalid for modulo operator.

OPTION2 (coalesce): error
Server: Msg 206, Level 16, State 2, Procedure VW_mytest3, Line 5
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Procedure VW_mytest3, Line 5
Operand data type numeric is invalid for modulo operator.

Try the second with ISNULL, sorry I had only tested the first method I posted and worked fine for me but my data types are INT for both columns instead of NUMERIC.  

(case when isnull([ppaddt],0) >= 17530101 then dateadd(minute,([ppadtm]/(100))*(60)+[ppadtm]%(100),CONVERT([char](8),[ppaddt],0))  end)

Server: Msg 206, Level 16, State 2, Procedure Test_mytest3, Line 5
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Procedure Test_mytest3, Line 5
Operand data type numeric is invalid for modulo operator.

Ok tested and added this for the numeric and modulo issue.  Not seeing that on my system, but have SQL Server 2005 installed; therefore, I wrapped the field with convert(int, ...) before using modulus operator.

(case when isnull([ppaddt],(0))>=(17530101) then dateadd(minute,floor([ppadtm]/(100))*(60)+convert(int,[ppadtm])%(100),convert([char](8),[ppaddt],(0)))  end)

Get these results:
PPADTM        PPADDT        Admit
153      20090701      7/1/2009 1:53:00 AM
1623      17530101      1/1/1753 4:23:00 PM
230      0      NULL
1213      56789      NULL

Hope that works as well for you.

here is the full sql

SELECT     PPGRP# AS PPGRPno, PPPAT# AS PPPATno, LEFT(DATENAME(year, CONVERT(datetime, CAST(PPADDT AS VARCHAR(10)), 112)), 4) AS PPAdYear, 
                      LEFT(DATENAME(MONTH, CONVERT(datetime, CAST(PPADDT AS VARCHAR(10)), 112)), 3) AS PPAdMonth, DATENAME(WEEKDAY, CONVERT(datetime, 
                      CAST(PPADDT AS VARCHAR(10)), 112)) AS PPAdWeekDay, SUBSTRING(CONVERT(VARCHAR, DATEADD(m, - 4, GETDATE()), 112), 1, 6) 
                      + '01' + '-' + CONVERT(varchar, GETDATE(), 112) AS PeriodPriorMonths, LEFT(CONVERT(varchar, GETDATE(), 112), 6) + '01' AS PeriodPriorMonth2, 
                      LEFT(DATENAME(quarter, CONVERT(datetime, CAST(PPADDT AS VARCHAR(10)), 112)), 4) AS Qtr, dbo.ufn_Grp2HR(PPADTM) AS TimeFrame, 
                      RIGHT('0000' + CAST(1 * CAST(PPADTM AS decimal(4, 0)) AS varchar(20)), 4) AS AdmitTime, PPADDT, PPADTM, PPDSDT, PPDSTM, DATEDIFF(yyyy, 
                      CONVERT(datetime, CAST(PPPDOB AS VARCHAR(10)), 112), CONVERT(datetime, CAST(PPADDT AS VARCHAR(10)), 112)) AS CurrendAge, 
                      CONVERT(datetime, CONVERT(char(8), PPADDT), 112) AS Expr1, (CASE WHEN isnull([ppaddt], 0) >= 17530101 THEN dateadd(minute, ([ppadtm] / (100)) 
                      * (60) + [ppadtm] % (100), CONVERT([char](8), [ppaddt], 0)) END) AS GrpDay2
 
FROM         dbo.HPPATMFL

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

??

I am sorry, I thought you were following Rob's advice of using this in a computed column.  Let me look at the SQL and get back to you.  My tests were in a computed column as you can see in the results I posted.

Hopefully I don't have any type-o's that get you lost on my point which I had suggested earlier (use a derived table)...

As you can see I am getting necessary columns and converting values to dates in the derived table to make use of SQL date functions outside of it.

Hope that makes sense.

select [PPGRP#] as PPGRPno, [PPPAT#] as PPPATno,
datename(year, admit) as PPAdYear,
left(datename(month, admit),3) as PPAdMonth,
datename(weekday, admit) as PPAdWeekDay,
convert(char(8), dateadd(m, datediff(m, 0, getdate())-4, 0), 112) 
  + '-' + convert(char(8), getdate(), 112) as PeriodPriorMonths,
convert(char(8), 
   dateadd(m, datediff(m, 0, getdate()), 0),
   112) as PeriodPriorMonth2, 
left(datename(quarter, admit), 4) AS Qtr,
dbo.ufn_Grp2HR(PPADTM) AS TimeFrame,
convert(char(5), admit, 108) AS AdmitTime,
PPADDT, PPADTM, PPDSDT, PPDSTM,
datediff(year, dob, admit) AS CurrendAge, 
datediff(day, admit, discharge) lsod,
datediff(hour, admit, discharge) lsoh,
datediff(minute, admit, discharge) lsom
from (
	select [PPGRP#], [PPPAT#],
	PPADDT, PPADTM, PPDSDT, PPDSTM, PPPDOB,
	-- calculated dates
	case when [ppaddt]>=(17530101) 
    	   then dateadd(minute,
	      floor([ppadtm]/(100))*(60)
	         + convert(int,[ppadtm])%(100),
	      convert([char](8),[ppaddt], 0)
	   )  
	end as admit,
	case when [ppdsdt]>=(17530101) 
	   then dateadd(minute,
	      floor([ppdstm]/(100))*(60)
		+convert(int,[ppdstm])%(100),
	      convert([char](8),[ppdsdt], 0)
	   )  
	end as discharge,
	case when [pppdob]>=(17530101) 
	   then convert(datetime, 
		convert([char](8),[pppdob], 0), 
		112
	   )
	end as dob
    from dbo.HPPATMFL
) t
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:

Wow - I got up this morning to see a lot of activity on this question.

It's great you've found the problem data. And yes, I'd be wrapping the expressions up in CASE to handle the bad rows.

You may prefer to use 19000101, rather than 1753... but it depends on if you're SQL 2008 or SQL 2005.

Rob

I added another 50 points -- you guys are awesome.