DateDiff and ODBC

[Van T. Dinh]

You back-end database engine does not have an in-built
DateDiff function or its usage requires a different syntax
from Access DateDiff() function.

You will need to check Help / Documentation for the back-
end database enigine for the equivalent function / correct
syntax. In general, I *always* check the SQL in the back-
end database engine because there are differences between
different flavours of SQL, in particular the in-built
function.

HTH
Van T. Dinh
MVP (Access)

 

[Peter Carlson]

The back end database is jet / access through ODBC. And I have checked the
sql in Access query

Peter

 

[Alick [MSFT]]

Hi Peter,

Would you please post the related code for calling SQLExecDirect?



Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.




--------------------
| From: "Peter Carlson" <[email protected]>
| References: <[email protected]> <02be01c37031$c7b23800
|
| The back end database is jet / access through ODBC. And I have checked
the
| sql in Access query
|
| Peter
|
message
| | > You back-end database engine does not have an in-built
| > DateDiff function or its usage requires a different syntax
| > from Access DateDiff() function.
| >
| > You will need to check Help / Documentation for the back-
| > end database enigine for the equivalent function / correct
| > syntax. In general, I *always* check the SQL in the back-
| > end database engine because there are differences between
| > different flavours of SQL, in particular the in-built
| > function.
| >
| > HTH
| > Van T. Dinh
| > MVP (Access)
| >
| >
| >
| >
| > >-----Original Message-----
| > >the following sql with {AlarmDate} and {PersonID} being
| > filled in with
| > >'07/13/2003' and 2 respectively
| > >
| > >SELECT *, FORMAT(dob, 'mm/dd/yyyy') as DOB2, DateDiff
| > ("yyyy", dob,
| > >'{AlarmDate}') + (Format('{AlarmDate}', "mmdd") < Format
| > (dob, "mmdd")) as
| > >Age from Person where id={PersonID};
| > >
| > >Works fine in Access as a query, but in our program
| > executed with
| > >SQLExecDirect returns SQL_NODATA_FOUND with a Functin
| > Sequence Error.
| > >
| > >Any ideas what I might be doing wrong? If I take out the
| > DateDiff, it works
| > >fine with SQLExecDirect
| > >
| > >Peter
| > >
| > >
| > >.
| > >
|
|
|

 

[Peter Carlson]

I've posted the pertinent code pieces...if you need more let me know. The
sql statement is loaded from a config file and the variables are run-time
replaced. I've copied / pasted the final SQL statement right into Access
and it works fine.

Peter


CString csSQLStatement = "SELECT *, FORMAT(dob, 'mm/dd/yyyy') as DOB2,
DateDiff("yyyy", dob,
'{AlarmDate}') + (Format('{AlarmDate}', "mmdd") < Format(dob, "mmdd")) as
Age from Person where id={PersonID};"

csSQLStatement.Replace("{AlarmDate}", csAlarmDate);
csSQLStatement.Replace("{PersonID}", csPersonID);


CODBCRecordset prs (_db);
if (!prs.Open((char*)csSQLStatement)) {
MessageBox (NULL, prs.GetError(), "Database Error", MB_OK);
}



/////////////////////////odbc recordset
CODBCRecordset(CODBCDatabase* pDb)
{
m_pDB = pDb;
m_hDbc = pDb->m_hDbc;
m_hStmt = NULL;
m_bIsEOF = FALSE;
m_bIsBOF = FALSE;
m_nRowsAffected = 0;
AllocStmt();
};

void CODBCRecordset::AllocStmt()
{
SQLAllocHandle(SQL_HANDLE_STMT, m_hDbc, &m_hStmt);
SQLRETURN retcode = SQLSetStmtAttr(m_hStmt,
SQL_ATTR_CURSOR_TYPE,(SQLPOINTER)SQL_CURSOR_STATIC, SQL_IS_INTEGER);
}

BOOL CODBCRecordset::Open(CHAR *szSqlStr)
{
SQLRETURN ret;
SQLINTEGER nRowCount;

ret = SQLExecDirect(m_hStmt, (SQLCHAR*)szSqlStr, SQL_NTS);
SQLRowCount(m_hStmt, &nRowCount);
m_nRowsAffected = nRowCount;

if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
SQLFetch(m_hStmt);
MoveFirst();
return TRUE;
}

m_bIsEOF = TRUE;
m_bIsBOF = FALSE;
return FALSE;
}

 

[Peter Carlson]

Any way then to calculate age using JET and SQL?
BTW, Format works fine with other queries.
Peter