How to get the Date portion of an SQLServer DateType field?

I'm trying to import an SQLServer table into Visual FoxPro (I've also posted
this in the SQLServer forum). I need to test the Date portion of a SQL Server
DateTime field against a user-entered Date.

I tried the following:

m_getagsc = SQLPREPARE(gnConnHandle, "SELECT * FROM AgentStateChange "+;
"WHERE AgentStateChange.ToState > 2"+;
" AND TTOD(AgentStateChange.Time) = ?m_rptdate", "agsc")

m_getagsc = SQLEXEC(gnConnHandle)

After this statement, m_getagsc = -1.

This works without the "AND TTOD(AgentStateChange.Time) = ?m_rptdate".

m_rptdate is a variable containing the user's entered date.
TTOD() is Visual FoxPro's function for returning the Date portion of a
DateTime field.

What am I doing wrong?

Thanks,
GaryZ

Olaf
Wed Jan 12 08:35:56 CST 2005

> m_getagsc = SQLPREPARE(gnConnHandle, "SELECT * FROM AgentStateChange "+;
> "WHERE AgentStateChange.ToState > 2"+;
> " AND TTOD(AgentStateChange.Time) = ?m_rptdate", "agsc")

> This works without the "AND TTOD(AgentStateChange.Time) = ?m_rptdate".

You can't use foxpro functions within the Select, that Select is
executed in SQL Server and must be SQL Server syntax without
foxpro function calls.

If m_rptdate is of type "D", then you might extend that to a time:

m_rptdate_begin = dtot(m_rptdate)
m_rptdate_end = dtot(m_rptdate+1)-1

then test for

"AND AgentStateChange.Time BETWEEN ?m_rptdate_begin AND ?m_rptdate_end"

Bye, Olaf.

Gene
Wed Jan 12 09:18:28 CST 2005

GaryZ wrote:
> I'm trying to import an SQLServer table into Visual FoxPro (I've also posted
> this in the SQLServer forum). I need to test the Date portion of a SQL Server
> DateTime field against a user-entered Date.
>
> I tried the following:
>
> m_getagsc = SQLPREPARE(gnConnHandle, "SELECT * FROM AgentStateChange "+;
> "WHERE AgentStateChange.ToState > 2"+;
> " AND TTOD(AgentStateChange.Time) = ?m_rptdate", "agsc")
>
> m_getagsc = SQLEXEC(gnConnHandle)
>
> After this statement, m_getagsc = -1.
>
> This works without the "AND TTOD(AgentStateChange.Time) = ?m_rptdate".
>
> m_rptdate is a variable containing the user's entered date.
> TTOD() is Visual FoxPro's function for returning the Date portion of a
> DateTime field.
>
> What am I doing wrong?
>
> Thanks,
> GaryZ
>



Use the MSSQL CONVERT function in the query

cQuery = "SELECT * FROM sometable WHERE CONVERT(varchar(10),
AgentStateChange.Time, 101) = '" & ?m_rptdate & "'"

Anders
Thu Jan 13 09:19:25 CST 2005

Gene
If you use remote views you can force data type changes with
DBSETPROP('view.field', 'FIELD', 'DataType', 'D') to turn a datetime into a
Date. Using SQLEXEC( ) there's no way to get a VFP type Date value. You can
get a character date only using a query with CAST() or CONVERT(9,. See SQL
Server's Books On Line documentation.
If the remote data is readonly you could move the cursor from your SQLExec
call to another cursor and convert at that point.
Using the CursorAdapter class might offer a way to do the conversion before
the CursorFill() call.
You could also fiddle with GUI, the grid or textbox or whaever where the
dates is displayed.
-Anders

"Gene Vital" <nothankyou@msnew.com> wrote in message
news:uZg26nL#EHA.2600@TK2MSFTNGP09.phx.gbl...
> GaryZ wrote:
> > I'm trying to import an SQLServer table into Visual FoxPro (I've also
posted
> > this in the SQLServer forum). I need to test the Date portion of a SQL
Server
> > DateTime field against a user-entered Date.
> >
> > I tried the following:
> >
> > m_getagsc = SQLPREPARE(gnConnHandle, "SELECT * FROM AgentStateChange "+;
> > "WHERE AgentStateChange.ToState > 2"+;
> > " AND TTOD(AgentStateChange.Time) = ?m_rptdate",
"agsc")
> >
> > m_getagsc = SQLEXEC(gnConnHandle)
> >
> > After this statement, m_getagsc = -1.
> >
> > This works without the "AND TTOD(AgentStateChange.Time) = ?m_rptdate".
> >
> > m_rptdate is a variable containing the user's entered date.
> > TTOD() is Visual FoxPro's function for returning the Date portion of a
> > DateTime field.
> >
> > What am I doing wrong?
> >
> > Thanks,
> > GaryZ
> >
>
>
>
> Use the MSSQL CONVERT function in the query
>
> cQuery = "SELECT * FROM sometable WHERE CONVERT(varchar(10),
> AgentStateChange.Time, 101) = '" & ?m_rptdate & "'"