Datetime filter in Cursor

[mccartmd]

Dear VFP Pros
I have a warranty table with a field called "canceldate" which is in "DateTime" format.
I want to filter for a certain canceldates into the Cursor.
The value in field for example is: 12/31/2014 12:00:00 AM . . .which is Datetime format.
How can I modify or format my cursor to capture and view just that cancaldate?
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SELECT Warrantytable.prodname, Warrantytable.canceldate,;
Warrantytable.war_pk;
FROM Proddbc!warrantytable;
WHERE Warrantytable.canceldate = {12/31/2014 12:00:00 AM} into Cursor WarrantyExpired
browse
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Thanks in advance for any counsel!

 

[Olaf Doschke]

datetime(YYYY,MM,DD,hh,mm,ss)

or

{^YYYY-MM-DD hh:mm:ss}

{^12/31/2014 12:00:00 AM} might also work out, but the format for datetime literals working independant on any settings is using - instead of / and no AM/PM.

Bye, Olaf.

 

[Dan Freeman]

If you're trying to match just the date (ignoring the time part) look up the Ttod() function.

WHERE Ttod(theDateTime) = Date(yyyy,yy,dd)

 

[wtotten]

If I may, a simple typo fix to DanFreeman's comment.
it should be:
WHERE TTOD(theDateTime) = DATE(yyyy,mm,dd) (he has it as yyyy,yy,dd)

Bill

 

[craigber]

Good options, but not Rushmore optimizable unless you have an index on TTOD(theDateTime).
Try this instead

Before the query:
endDate = testDate + 1

then use testDate for the first part of the query and endDate for the second part

WHERE theDateTime >= DATETIME(yyyy, mm, dd) AND theDateTime <= DATETIME(yyyy, mm, dd)

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Olaf Doschke]

Craig, I think you meant to say

1. Initialise two variables:
testdate = Date(2014,12,31)
enddate= testdate+1
2. Then query
...WHERE canceldate>=testdate and canceldate<enddate
or
...WHERE canceldate Between testdate and enddate

It's a good idea to test a datetime against a range rather than a precise value.

@mccartmd, it's even simpler to find all records already having expired with
... WHERE canceldate < DateTime()

and to see which records will expire within one year from now:
... WHERE canceldate between DateTime() And GoMonth(DateTime(),12)

You can solve many needs without any literal value, as Date() is todays date and DateTime() is now (with seconds precision).

Bye, Olaf.

 

[craigber]

Thanks Olaf,

Yup, That's what I meant.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog