topic Re. :Subtract two date fields to get time difference in minutes. in QlikView

Subtract two date fields to get time difference in minutes.

Thu, 29 Jul 2010 13:28:29 GMT

Hi,

I have date's stored in a SQL database, the field type being datetime. Date is stored in the 'YYYY-MM-DD hh:mm:ss tt' format.

tt being AM / PM.

I need to retrieve only the time part out of this date. i.e Time(StopDate) - Time(StartDate)=Time in minutes.

Example:

StopDate: 1899-12-30 1:10:00 PM

StartDate: 1899-12-30 9:14:00 AM

So I need something like : 1:10:00 PM - 9:14:00 AM = 236 minutes.

Any suggestions on how this can be done?

Secondly, how to get a count of NULL values of a datetime field? I tried something like : if(isnull(Date),nullcount(Date)). But this returns slightly different values as compared to a similar query written in SQL.

Thanks in advance.

Re. :Subtract two date fields to get time difference in minutes.

martin59 — Thu, 29 Jul 2010 14:21:56 GMT

interval(StopDate-StartDate,'mm')

Hope it helps you

Re. :Subtract two date fields to get time difference in minutes.

Fri, 30 Jul 2010 05:27:50 GMT

Thanks for your reply. But it didn't work out for me. I tried :

interval('1899-12-30 1:10:00 PM'-'1899-12-30 9:10:00 AM', 'mm')

It gives a '-' i.e null as result.

The 'tt' i.e AM/PM here seems to be the problem. How do I get rid of the 'tt' part, because the date is stored in SQL in that particular format I mentioned earlier. Do I need to truncate the 'tt' part from the date string? But how would it know what time period it is subtracting 'AM/PM'? Really confused on this part.

The following expression seems to work absolutely fine giving result as 240 minutes:

interval('1:10:00 PM'-'9:10:00 AM', 'mm')

So the time part needs to be retrieved from the date.

Any suggestions?

Re. :Subtract two date fields to get time difference in minutes.

Fri, 30 Jul 2010 05:58:05 GMT

Hey I found out how that can be done:

=interval(Right('1899-12-30 12:09:00 PM',10)-Right('1899-12-30 8:33:00 AM',10),'mm')

But my second question is still pending. How to get a count of NULL values of a datetime field? I tried something like : if(isnull(Date),nullcount(Date)). But this returns slightly different values as compared to a similar query written in SQL.

Thanks.