Filter date and time

[jhonny03]

how to filter this:

November 15, 2015, 8:03:12 AM

im trying to use this:

set FILTER TO utype_table.time=DTOC(thisform.olecontrol1._Value)

but this is to filter for the month only..
tnx in advance

 

[Mike Gagnon]

Rule #1
Never use a SET FILTER TO.
Rule #2
What is the format of utype_table.time and thisform.olecontrol1.value
Are they both date in a character format ?



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Olaf Doschke]

Well, to put Mikes question of Rule#2 into a rule: Storey types in their native type and compare them in their native format.

So if the type of the utype_table.time field is char, that's wrong, it should be datetime. Just for one simple reason: You can't make date calculations (compute differences, compare, filter time spans) with char data, you can only do thst with the datetime type made for datetime data.

Bye, Olaf.

 

[jhonny03]

sory sir..newbie here..
they are both character.

 

[Olaf Doschke]

DTOC converts to a wide variety of character formats. So you better change the time field to store datetimes. And if you store TIME() to it, you have a clock time string without date, that's got to be changed to using DATETIME().

These rules are not only true for VFP.

Bye, Olaf.

 

[Mike Lewis]

Johnny,

It's not clear what you want to achieve.

You say you want to set a filter where a date is equal to November 15, 2015, 8:03:12 AM. Your code shows that you are filtering where the date equals a date selected from an OLE control (presumably a calendar of some sort). But then you say that code is only filtering on the month, which doesn't look right to me.

Or, or are you saying that the code is correctly filtering on the date selected from the control, but you want to change it so that it only filters on the month? If that's the case, you need to use the MONTH() and YEAR() functions to extract the relevant month. But that only works on date and datetime data types, so you need to do a conversion first.

Something like this:

SET FILTER TO MONTH(CTOT(utype_table.time)) = MONTH(thisform.olecontrol1._Value) ;
  AND YEAR(CTOT(utype_table.time)) = YEAR(thisform.olecontrol1._Value)

That will filter records where the month of the date in the table is equal to the month of the date selected by the control. But I'm guessing that is what you want to achieve, as your original question is most unclear.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

While that SET FILTER could work, it has a very undesirable condition: utype_table.time has to be the format CTOT can convert. Even if that is so, the CTOT conversion can fail on changesd setting, because the current character format for datetimes depends on locale and even if VFP settings override that it depends on several VFP settings for century, order of year month and date and the separator used, besides also AM/PM vs 24 hours time format.

If code is needing conversions its better to adapt the data itself, as said there is no reason to store datetimes in char fields, there are datetime fileds for that matter and it's easier to compute with them and compare them, thereby also filter on them.

The original code you have would then be simpified to set FILTER TO utype_table.time=thisform.olecontrol1._Value, but it will only filter for the very specific time you select from the ole control.

If you want to filter for a month, as Mike better spotted than me, you either store date parts separate, eg also the month has its own field, or you use the YEAR() and MONTH() functions on a datetime. There are rules against data redundancy that would suggest you don't store the month and ddatetime, as they could contradict each other and as Tamar once quotes, the man with one clock knows the time, the man with two clocks never can be sure. Another way to get at the month would be defining an index on MONTH(datetimefield) and another one on YEAR(datetimefield) and then let a condition as MONTH(datetimefield)=MONTH(datetimepickedfromacontrol) AND YEAR(datetimefield)=YEAR(datetimepickedfromacontrol) be optimized by that indexes.

Bye, Olaf.

 

[Mike Lewis]

One other point to add to your difficulties:

I'd be very cautious about using THISFORM in a filter. It's bound to lead to problems if the filter condition is evaluated outside the context of the form: either an error or an incorrect filter being applied. It's for the same reason that you wouldn't normally use a local variable in a filter.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[jhonny03]

already converted it to datetime and
i got an error
"function argument,value or count is invalid"
on the first line sir mike

 

[Olaf Doschke]

Well, because now you don't need to convert to datetime anymore.

Bye, Olaf.

 

[Mike Lewis]

The error might be because [tt]thisform.olecontrol1._Value[/tt] is a character string, so you would need to convert it to date first:

So, instead of:

[tt]MONTH(thisform.olecontrol1._Value)[/tt]

it would be:

[tt]MONTH(CTOD(thisform.olecontrol1._Value))[/tt]

And similarly with YEAR().

Or it might be because [tt]utype_table.time[/tt] is already a date or datetime, in which case you do NOT ned to convert it:

[tt]MONTH(utype_table.time)[/tt]

Again, similarly with YEAR().

If you have control over the data types, I suggest you store them all as dates or datetimes in the first place, to avoid any confusion.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ramani]

In a filter condition, make sure the data type is same on either side.
DTOS() function is ideal to use instead of DTOC() - if only date needs to be filtered.
DTOS will return values in YYYYMMDD format.

DTOC will return in SET date format order and so could return MMDDYYY etc depending on your SET DATE. In a filter this will not give desired results.

Have a great time friends

____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/