Filter on DateTime field

[Niels Larsen]

Hi

I have a table with a DateTime field (mssql). I Clarion i have a group over a string( (as I always have when using date and time).
But I have a lot of problems when i want to filter on the time part.

I've tried with the filter on the string and the two fields date / time. It makes no difference - all as hotfields.
Is there anything I should pay particular attention to when I work with time in a filter in NTWS?

Regards Niels

[kevin plummer]

Can you post your fllter so we can have a look?

[Niels Larsen]

Hi Kevin.

I've tried several different ways.

1. filter = 'FIL:Date = LOC:Date AND FIL:Time = LOC:Time'

2. filter = 'FIL:Date = ' & LOC:Date & ' AND FIL:Time = ' & LOC:Time

3. filter = 'FIL:DateTime = LOC:DateTime'
(FIL:DateTime is the string( below the DateTimeGroup)

4. filter = 'FIL:DateTime = '& LOC:DateTime

All as hotfields and as not hotfields .

all 4 ways gives filter on the date but not on the time. They all returns all the times for one day.

[Bruce]

So let's trip out the noise.

>> All as hotfields and as not hotfields .

Hotfields have nothing to do with Filters. I want you and everyone else to internalize this please. Chant it as a mantra over breakfast. Hotfields have nothing to do with filters. Not in Clarion Win32, not in NetTalk, not anywhere.

Hotfields are nothing more or less than adding fields to the View. That's all. Filtered variables do not need to be in the view. Ergo, no connection.

>> 1. filter = 'FIL:Date = LOC:Date AND FIL:Time = LOC:Time'

this kind of filter will not work because you are using a local variable _inside_ the filter string. It's not necessary to do this, and it's just bad.


>> 2. filter = 'FIL:Date = ' & LOC:Date & ' AND FIL:Time = ' & LOC:Time

This to me looks like the best one - assuming that Loc:Date and loc:Time are primed correctly. (Your problem is probably that they're not.)

So using this filter, turn on the option to "Display Filter & Order in Debugview" and paste the actual contents of the filter here. I'm guessing it'll be something like;

FIL:Date =0 AND FIL:Time = 0

Cheers
Bruce

[Niels Larsen]

If I use this filter: 'FIL:Dat=' & TODAY() I get all records from today, but only those with the time 0 (DebugView shows FIL:Date = 77262)
If I use this filter: 'FIL:Date='&TODAY()&' AND FIL:Time=3600001' no records shows.(DebugView shows FIL:Date=77262 AND Fil:Time=3600001)

If I remove the filter I can se a lot of records from today (77262) with the time 3600001.

I usually have no problems working with filters, but this I just can't figure out.

/Niels

[Bruce]

I think you are not counting your zeros correctly in 3600001.
I'm suspecting a mismatch between your filter and the data.

[Niels Larsen]

No, the values for date and time is right. 10am = 10*60*60*100+1 = 3600001.
If I remove the filter I have lots of records with my filter values.

[kevin plummer]

try something like

'FIL:Date='&TODAY()&' AND FIL:Time>3600000'

to test Bruce's theory.

I've come across precision problems between SQL and clarion before.

[Niels Larsen]

Hi Kevin.

Tried your suggestion but it made no difference.
Then i tried this: 'SQL(A.TIDSPUNKT=''2012-06-28 01:00:00.000'')' ..... and it works great.

I'm still puzzled why I can not filter on the time field. But for now I have to "settle" with the SQL() option.

Thanks for now - over and out.
/Niels