Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

where clause for time

Hi,

I'm doing a simple where clause on sales_time

Where sales_time < '11:59:59.000'

sample of sales_time values

02:39:14.000

02:40:40.000

02:47:32.000

04:00:00.000

04:01:28.000

04:45:02.000

04:50:00.000

05:00:00.000

and it returns 0 lines.

i tried num(sales_time ) or time(sales_time) or timestamp(sales_time) or num(timestamp(sales_time)), none of it works.

help please ....

7 Replies
Anonymous
Not applicable
Author

where time(time#(sales_time, 'hh:mm:ss.fff' ) < time(time#(sales_time, '11:59:59.000' , 'hh:mm:ss.fff' )

Not applicable
Author

Hi,

I would use the hour(), minute() and second() functions

sth like

WHERE hour(sales_time)*3600+minute(sales_time)*60+second(sales_time) < 11*3600+59*60+60

Fabrice

luis_pimentel
Partner - Creator III
Partner - Creator III

Make sure your TimeFormat definition looks like:

SET TimeFormat='hh:mm:ss[.fff]';

Then, the comparision should work.

maxgro
MVP
MVP

it seems your filter works for me, with this

Where sales_time < '02:59:59.000';

I get 3 rows,

Here the script

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

load *  Where sales_time < '02:59:59.000';

//Where sales_time < '11:59:59.000'

//

//sample of sales_time values

LOAD * INLINE [

sales_time

02:39:14.000

02:40:40.000

02:47:32.000

04:00:00.000

04:01:28.000

04:45:02.000

04:50:00.000

05:00:00.000

];

Not applicable
Author

Bill,

You have the closest solution.

but with this sequence time#(time()).

thx

Not applicable
Author

Hi Fabrice,

Yes, it'd work that way, and i considered it as "best practice". thanks.

Not applicable
Author

Try this


where sales_time< time(time#(sales_time, '11:59:59.000' , 'hh:mm:ss.fff' )