Solved

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

Posted on 1998-09-30
3
216 Views
Last Modified: 2010-04-01
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
Comment
Question by:dekoay
3 Comments
 

Expert Comment

by:mmachie
Comment Utility
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
 

Author Comment

by:dekoay
Comment Utility
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
 

Accepted Solution

by:
sava earned 50 total points
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction This article is a continuation of the C/C++ Visual Studio Express debugger series. Part 1 provided a quick start guide in using the debugger. Part 2 focused on additional topics in breakpoints. As your assignments become a little more …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now