SQL Query using date format problem!!!
-
ive been creating a query using date as one of its parameters. the date is of CTime data type. my code is like this: CString dbWorkName; CString dbPICName; CString dbElementName; CTime dbDate; CString readSQL; double ReviewActualHour = 0; CString strReviewActualHour; CString ProjName; CString strReviewDay; SYSTEMTIME date; dbDate.GetAsSystemTime(date); strReviewDay.Format("%04d-%02d-%02d",date.wYear,date.wMonth,date.wDay); _bstr_t bstrQuery; _variant_t getFiled; _variant_t vRecsAffected; vRecsAffected = 0L; readSQL = "SELECT L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd, Sum(L_EveryDay.difference)/60 AS TOTALHOUR "; readSQL += "FROM T_OccasionName INNER JOIN (T_ProjectDetailName INNER JOIN (T_ProjectName INNER JOIN L_EveryDay ON T_ProjectName.seq = L_EveryDay.project) ON T_ProjectDetailName.seq = L_EveryDay.projectdetail) ON T_OccasionName.seq = L_EveryDay.occasion "; readSQL += "GROUP BY L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd "; readSQL += "HAVING (((L_EveryDay.username)="; readSQL += '"'; readSQL += dbPICName; readSQL += '"'; readSQL += ") "; readSQL += "AND ((T_ProjectName.project) LIKE "; readSQL += '"'; readSQL += '%'; readSQL += ProjName; readSQL += '"'; readSQL += ") "; readSQL += "AND ((T_ProjectDetailName.detail)="; readSQL += '"'; readSQL += dbWorkName; readSQL += '"'; readSQL += ") "; readSQL += "AND ((T_OccasionName.occasion)="; readSQL += '"'; readSQL += dbElementName; readSQL += '"'; readSQL += "AND ((L_EveryDay.ymd)="; readSQL += "#"; readSQL += strReviewDay; readSQL += "#"; readSQL += "));"; bstrQuery = readSQL; MessageBox(NULL, readSQL, "Test", MB_OK); try { recordSet_ = connection_->Execute(bstrQuery,&vRecsAffected,adOptionUnspecified); } catch( _com_error &errMessage ) { MessageBox(NULL, "failed", "Test", MB_OK); TRACE(errMessage.Description()); TRACE(errMessage.ErrorMessage()); TRACE(bstrQuery); } When the program executes the query, runtime error appears. im having difficulty on how to format the date during the query. In MS Access, the date is enclosed with #. So, i tried the same way too. I tried so many times on formatting the date but no one returns the good result.. so for those, who have encountered the same problem as I am, please help me with this... what should I do with my query? thanks in advance
-
ive been creating a query using date as one of its parameters. the date is of CTime data type. my code is like this: CString dbWorkName; CString dbPICName; CString dbElementName; CTime dbDate; CString readSQL; double ReviewActualHour = 0; CString strReviewActualHour; CString ProjName; CString strReviewDay; SYSTEMTIME date; dbDate.GetAsSystemTime(date); strReviewDay.Format("%04d-%02d-%02d",date.wYear,date.wMonth,date.wDay); _bstr_t bstrQuery; _variant_t getFiled; _variant_t vRecsAffected; vRecsAffected = 0L; readSQL = "SELECT L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd, Sum(L_EveryDay.difference)/60 AS TOTALHOUR "; readSQL += "FROM T_OccasionName INNER JOIN (T_ProjectDetailName INNER JOIN (T_ProjectName INNER JOIN L_EveryDay ON T_ProjectName.seq = L_EveryDay.project) ON T_ProjectDetailName.seq = L_EveryDay.projectdetail) ON T_OccasionName.seq = L_EveryDay.occasion "; readSQL += "GROUP BY L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd "; readSQL += "HAVING (((L_EveryDay.username)="; readSQL += '"'; readSQL += dbPICName; readSQL += '"'; readSQL += ") "; readSQL += "AND ((T_ProjectName.project) LIKE "; readSQL += '"'; readSQL += '%'; readSQL += ProjName; readSQL += '"'; readSQL += ") "; readSQL += "AND ((T_ProjectDetailName.detail)="; readSQL += '"'; readSQL += dbWorkName; readSQL += '"'; readSQL += ") "; readSQL += "AND ((T_OccasionName.occasion)="; readSQL += '"'; readSQL += dbElementName; readSQL += '"'; readSQL += "AND ((L_EveryDay.ymd)="; readSQL += "#"; readSQL += strReviewDay; readSQL += "#"; readSQL += "));"; bstrQuery = readSQL; MessageBox(NULL, readSQL, "Test", MB_OK); try { recordSet_ = connection_->Execute(bstrQuery,&vRecsAffected,adOptionUnspecified); } catch( _com_error &errMessage ) { MessageBox(NULL, "failed", "Test", MB_OK); TRACE(errMessage.Description()); TRACE(errMessage.ErrorMessage()); TRACE(bstrQuery); } When the program executes the query, runtime error appears. im having difficulty on how to format the date during the query. In MS Access, the date is enclosed with #. So, i tried the same way too. I tried so many times on formatting the date but no one returns the good result.. so for those, who have encountered the same problem as I am, please help me with this... what should I do with my query? thanks in advance
Use parameters. You'll need a Command object. Parameters send the values directly to the database engine with no translation to and from ambiguous string formats. Stability. What an interesting concept. -- Chris Maunder
-
ive been creating a query using date as one of its parameters. the date is of CTime data type. my code is like this: CString dbWorkName; CString dbPICName; CString dbElementName; CTime dbDate; CString readSQL; double ReviewActualHour = 0; CString strReviewActualHour; CString ProjName; CString strReviewDay; SYSTEMTIME date; dbDate.GetAsSystemTime(date); strReviewDay.Format("%04d-%02d-%02d",date.wYear,date.wMonth,date.wDay); _bstr_t bstrQuery; _variant_t getFiled; _variant_t vRecsAffected; vRecsAffected = 0L; readSQL = "SELECT L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd, Sum(L_EveryDay.difference)/60 AS TOTALHOUR "; readSQL += "FROM T_OccasionName INNER JOIN (T_ProjectDetailName INNER JOIN (T_ProjectName INNER JOIN L_EveryDay ON T_ProjectName.seq = L_EveryDay.project) ON T_ProjectDetailName.seq = L_EveryDay.projectdetail) ON T_OccasionName.seq = L_EveryDay.occasion "; readSQL += "GROUP BY L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd "; readSQL += "HAVING (((L_EveryDay.username)="; readSQL += '"'; readSQL += dbPICName; readSQL += '"'; readSQL += ") "; readSQL += "AND ((T_ProjectName.project) LIKE "; readSQL += '"'; readSQL += '%'; readSQL += ProjName; readSQL += '"'; readSQL += ") "; readSQL += "AND ((T_ProjectDetailName.detail)="; readSQL += '"'; readSQL += dbWorkName; readSQL += '"'; readSQL += ") "; readSQL += "AND ((T_OccasionName.occasion)="; readSQL += '"'; readSQL += dbElementName; readSQL += '"'; readSQL += "AND ((L_EveryDay.ymd)="; readSQL += "#"; readSQL += strReviewDay; readSQL += "#"; readSQL += "));"; bstrQuery = readSQL; MessageBox(NULL, readSQL, "Test", MB_OK); try { recordSet_ = connection_->Execute(bstrQuery,&vRecsAffected,adOptionUnspecified); } catch( _com_error &errMessage ) { MessageBox(NULL, "failed", "Test", MB_OK); TRACE(errMessage.Description()); TRACE(errMessage.ErrorMessage()); TRACE(bstrQuery); } When the program executes the query, runtime error appears. im having difficulty on how to format the date during the query. In MS Access, the date is enclosed with #. So, i tried the same way too. I tried so many times on formatting the date but no one returns the good result.. so for those, who have encountered the same problem as I am, please help me with this... what should I do with my query? thanks in advance
Like Mike said, using parameters is best way to do it. But if you want to build your own SQL statement, try to format the date into yyyy-mm-dd or yyyy/mm/dd format in the SQL statement, e.g. for Access it is #1999/05/24# By using this you'll: 1. Remove ambiguity between date and month. 2. Remove error when passing date into SQL statement. Cheers, Edbert P.