Selecting / filtering based on two fields 'Date' and 'Time'

[steve4king]

DELE FROM INVOICE_VOID WHERE (DTOC(DATE) + " " +(TIME)) < (SELECT (DTOC(MAX(DATE)) + " " + (TIME)) FROM CASH_OLD)

This does not work obviously as it tries filtering the date after it has changed it to a character.. thus (12/01/01 04:15) > (09/21/07 01:22)

I figured this out so changed the code to:

DELE FROM CASH_VOID WHERE (DTOC(DATE) + " " +(TIME)) < (SELECT (DTOC(MAX(DATE)) + " " + (TIME)) FROM CASH_OLD)

This fixed the initial select, but when comparing to the other table it is comparing as text again.

Do I need to convert it back to a date before comparing? If so how can I do this?

Thanks in advance,

-Steve W.

 

[jimstarr]

Hi Steve,

Use your existing code but proceed it with:

SET DATE YMD


Jim

 

[Olaf Doschke]

you're having a date field called date and a field time with a character of the time?

Is the time formatted like 13:15 ? or 01:15 pm ?

My general idea would be to convert the time to seconds and add that to DTOT(date) to get a datetime, then compare these.

Bye, Olaf.

 

[steve4king]

Olaf, the time is character, formatted like 13:15.

Jim, thanks, that got me a lot closer.. Then just had to set century on.

Thanks for the help guys.

 

[Mike Lewis]

Steve,

I'm not suggesting you change what you've got, but for future reference, it's worth noting that DTOC() is sensitive to several settings, such as SET DATE, SET MARK, SET CENTURY, etc. -- as you have discovered.

A better approach would be to use DTOS(), which avoids all those dependencies.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[steve4king]

Thanks Mike,
I'm not sure I understand however:
How would it be able to sort the date properly if converted to a string since I cannot change to century/YMD?

-Steve

 

[CajunCenturion]

The DTOS function returns a character string representation of date in YYYYMMDD format regardless of any settings. This format lends itself to sorting and date comparisons.




--------------
Good Luck
To get the most from your Tek-Tips experience, please read
FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Mike Lewis]

Steve,

As CajunCenturion pointed out, DTOS() is designed specifically to convert to a string that's amenable to sorting. It's often used to create an index on a date when you don't want to dependend on any particular date format.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

As long as you can compare or index on a date or datetime field (or compare dates) an the base of a date(time) field, I'd not use any character transformations, not even DTOS.

DTOS is good, if you need it in a compund index, concatenating more fields. DTOS() is also nice for filenames that should sort themselves accordingly.

Bye, Olaf.