Problem to the script

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
Here is the error
 
You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '%m-%d %H:%i:%s') as in_ti
me_dscr,  date_format(p.stockout_time,'%Y-%m-%d %H:%i:%' at line 1
 

Open in new window

got within VS, while this is the script
 
                sql = @"select " +
                    " null as row_num, " +
                    " date_format(p.in_time,'%Y-%m-%d %H:%i:%s') as in_time_dscr, " +
                    " date_format(p.stockout_time,'%Y-%m-%d %H:%i:%s') as stockout_time_dscr, " +
                    " date_format(ADDDATE(p.store_rcv_time, " + package_expiry_day_vg.ToString() + "), '%c月%e日') as expiry_date_chi, " +
                    " date_format(ADDDATE(p.store_rcv_time, " + package_expiry_day_vg.ToString() + "), '%D %b') as expiry_date_eng, " +
                    " c_s.sms_addr_eng, " +
                    " c_s.sms_addr_chi, " +
                    " c_s.store_no, " +
                    " p.*  " +
                    " from package_vg p  " +
                    " left join c_store_vg c_s on c_s.dscr = p.store_dscr " +
                    " where p.deleted = '0' " +
                    " and (p.phone like '9%' or p.phone like '6%' or p.phone like '5%') " +
                    " and length(trim(p.phone)) = 8 " +
                    " and c_s.send_sms = 1 " +
                    " and (p.status = 'store_rcv' or p.status = 'store_app_rtn') " +
                    " and p.sms_send = 0 " +
                    " and date_format(p.store_rcv_time,'%Y-%m-%d') >= " + DBUtil.GetSqlString(From_Date.ToString(DBUtil.DATE_FORMAT)) + " " +
                    " and date_format(p.store_rcv_time,'%Y-%m-%d') < " + DBUtil.GetSqlString(To_Date.ToString(DBUtil.DATETIME_FORMAT)) + " " +
                    " order by p.id";
					

Open in new window

why?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
Why do you compare strings in the first place? This leads normally to a table scan. Also use a parameterized query, not a manually crafted SQL string.
HuaMin ChenProblem resolver

Author

Commented:
Sorry, but why do I get the problem mentioned?
ste5anSenior Developer

Commented:
Use line breaks to get a better line number..

Also output the string and before executing in and run it in the workbench.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial