Solved

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

Posted on 1998-09-30
3
221 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
Article by: SunnyDark
This article's goal is to present you with an easy to use XML wrapper for C++ and also present some interesting techniques that you might use with MS C++. The reason I built this class is to ease the pain of using XML files with C++, since there is…
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 pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

691 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