• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Problem with SQL Server Date... Need experts Help pls

Hi Experts! I need yur assistance to solve SQL Server 6.5 date problem.

I had a program written in C++ Connect to SQL Server via ODBC Driver. Below is the partial statement :

if (SQL_SUCCESS == (rc = ::SQLAllocHandle(SQL_HANDLE_STMT,     hdbc, &hstmt)))
{
    strcpy(statement, "SELECT fldItemformula from            tblformula WHERE flditemcode = ");
    strcat(statement, "'");
    strcat(statement, ITEMCODE);
    strcat(statement, "'");
    strcat(statement, " AND DatePart('d',                fldReportoingDate)= ");
    strcat(statement, "DatePart('d', '");
    strcat(statement, REPORTDAY);
    strcat(statement, "')");
      
    LPCSTR szSQL = statement;
    if (SQL_SUCCESS == (rc = ::SQLPrepare(hstmt,(unsigned         char*)szSQL, SQL_NTS)))
    {
      if (SQL_SUCCESS == (rc = ::SQLExecute(hstmt)))
      {
         SDWORD cb;
         double iT_Amt;
              SQLBindCol(hstmt, 1, SQL_C_DOUBLE, &iT_Amt, 0,            &cb);
         rc = SQLFetch(hstmt);
           etc .. etc

This statement in which I used in MSAccess 97, it return me a value but unfortunately, when I connect to SQLServer 6.5, using the same statement, it return me a false value. When I debug this program by using this statement :

'SELECT fldItemFormula FROM tblformula WHERE fldItemcode = ITEMCODE'

it does return me the value. It seems like the problem is cause by the date. However, when I use the statement in Visual Data Manager (VB5), the staement is correct. I hope that someone would be kind enuff to help me solve this problem.
0
dekoay
Asked:
dekoay
1 Solution
 
mmachieCommented:
what are your variable types?

>strcat(statement, ITEMCODE);
>strcat(statement, REPORTDAY);
are ITEMCODE and REPORTDAY some sort of string?

>'SELECT fldItemFormula FROM tblformula WHERE fldItemcode = ITEMCODE'
looking at this statement it appears as if ITEMCODE is a numeric.

you might want to look at using CString szSQL and use the szSQL.Format() function to set up the select statement.
0
 
dekoayAuthor Commented:
All of them is a string...except fldreportingdate is a date (in SQL server). I use SQLPrepare to execute this statement... However, it work in MSAccess but not in SQLServer
0
 
savaCommented:
I think it is more SQL question. Your DATEPART function incorrect. Use: DatePart(day, fldReportoingDate). I hope it will solve your problem.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now