topic Time Format within IF Statement in QlikView

Time Format within IF Statement

Tue, 20 Dec 2011 15:49:26 GMT

I have a DateTime field in my database. I need an expression to show whether the time is "Day" or "Night". "Night" is defined as between 19:00 (7:00 PM) and 07:00 (7:00 AM). I need some help with the time syntax.

I tried these, but nothing is working so far:

IF(TIME(DateTime)>1900 OR TIME(DateTime)<0700,"Night","Day")

IF(TIME(DateTime)>19 OR TIME(DateTime)<07,"Night","Day")

IF(TIME(DateTime)>19:00 OR TIME(DateTime)<07:00,"Night","Day")

What am I doing wrong here? Is it a syntax problem with the times, or is it the IF statement?

Thanks,

Sam.

Re: Time Format within IF Statement

Miguel_Angel_Baeyens — Tue, 20 Dec 2011 16:05:59 GMT

Hi Sam,

It's rather a question of formatting. I'd use instead the InDayToTime function:

=If(InDayToTime(Time('23:00'), Time('19:00'), 0, Num(Time('07:00'))), 'Day', 'Night')

So the first parameter is the time, the second is the end of the day and the third is the beginning of the day. If InDayToTime returns -1 (true) then the time is between 07:00 and 19:00, so Day, otherwise Night.

Hope that helps.

Miguel

Re: Time Format within IF Statement

Tue, 20 Dec 2011 16:56:31 GMT

Miguel:

Thanks for the idea. I tried editing your expressions slightly, to read the time from my OrderDateTime field. Unfortunately, that expression is returning "Night" for every record, so there's still something wrong with the syntax:

=If(InDayToTime(Time(OrderDateTime,'hh:mm'), Time('19:00'), 0, Num(Time('07:00'))), 'Day', 'Night')

Any idea what I'm doing wrong?

Re: Time Format within IF Statement

Miguel_Angel_Baeyens — Tue, 20 Dec 2011 18:46:08 GMT

Hi Sam,

I'm pretty sure it's something related to formats. You need to extract the time part from your timestamp, something like

Time(Frac(OrderDateTime))

Hope that helps.

Miguel