Solved

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

Posted on 1998-09-30
3
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 

Expert Comment

by:mmachie
ID: 1174100
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
ID: 1174101
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
ID: 1174102
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Errors will happen. It is a fact of life for the programmer. How and when errors are detected have a great impact on quality and cost of a product. It is better to detect errors at compile time, when possible and practical. Errors that make their wa…
Unlike C#, C++ doesn't have native support for sealing classes (so they cannot be sub-classed). At the cost of a virtual base class pointer it is possible to implement a pseudo sealing mechanism The trick is to virtually inherit from a base class…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

726 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