where clause for time

[#1]

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 ....

[Anonymous]

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

[#3]

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]

Make sure your TimeFormat definition looks like:

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

Then, the comparision should work.

[maxgro]

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

];

[#6]

Bill,

You have the closest solution.

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

thx

[#7]

Hi Fabrice,

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

[#8]

Try this

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