How do I SELECT a DATETIME field in SQL? 2

[jrumbaug]

I have a question about how to SELECT a DATETIME field in a SQL statement. The database name is SCHEDULE. The DATETIME field name is APPOINTMENT. The following does NOT work:

SELECT * from SCHEDULE where appointment=date()

I know that appointment is a TIME variabel and that DATE() is a DATE variable. But from the command window I get can type:

test = DTOT(DATE())
? vartype( test )        && types T
? test = DATE()          && types .T.

I'm getting hung up on what works in the command window, does not work with the SQL statement. What is the preferred way to select a date from a DATETIME field in a SQL statement?

Jim Rumbaugh

 

[Mike Gagnon]

How about :

 SET CENTURY ON
SELECT * from SCHEDULE where ctod(SUBSTR(TTOC(appointment),1,10))=date()

Mike Gagnon

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

 

[jrumbaug]

Thanks Mike,

I cut and pasted your code and it works fine.
I also found out that this works:

SELECT * from SCHEDULE where  TTOC(appointment ) = DTOC(date())

I can move on now.

Jim Rumbaugh

 

[Mike Gagnon]

jrumbaug

I would be careful with your statement since you are comparing a DATETIME with a DATE value. Depending on your settings this might give you an unwnated result.

Mike Gagnon

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

 

[jrumbaug]

Thanks Mike,
This is my first round with DATETIME variables. I do not know all the pitfalls and "gotchas". I know the settings can affect the DATE() results, but do they also affect the DATETIME() results, and if so, do they affect them equally?

Jim Rumbaugh

 

[Mike Gagnon]

SELECT * from SCHEDULE where TTOC(appointment ) = DTOC(date())

I appears you rae trying to compare a DATETIME values with a date value, if might cause a problem since

? DATETIME() = DATE() && Returns .F.

Mike Gagnon

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

 

[terosoft]

You can use
SELECT * from SCHEDULE where DTOS(appointment) = DTOS(date())
instead.

Gerardo Czajkowski

 

[craigsboyd]

I'm confused, why the need to convert it into a string in order to do the comparison?

SELECT * from SCHEDULE where TTOD(appointment) = date()

...wouldn't that work?

http://www.sweetpotatosoftware.com

 

[jrumbaug]

Thank you Craig, BIG TIME.

That's a 'DOH' on me. It works, and I like it because it only uses one funtion call. I'm changing my cursor select statement to :
SELECT * from SCHEDULE where TTOD(appointment) = date()

Actaully I'm changing the system variable _DIARYDATE before the call so now it's:
SELECT * from SCHEDULE where TTOD(appointment)=_DIARYDATE

Gerado
SELECT * from SCHEDULE where DTOS(appointment) = DTOS(date())
Should not work since appointment is a DateTime field not a date. There is not a TTOS() function.

Mike
You are correct that:
? DATETIME() = DATE() && Returns .F.
But in the command window, I can still type:
test = DTOT(DATE())
? vartype( test ) && returns T
? test = DATE() && returns .T.
Which started my confusion,because here a TIME Var = DATE Var returns TRUE.

Thanks everyone, I've learned SOOO much
By The Way. I now have a working schedule program.
Jim Rumbaugh