Originally posted: http://community.9cbs.net/expert/faq/faq_index.asp? Id = 207897
Environment: SQL Server2000 SP4
Question: SELECT dateDiff (day, '20040910', '20040920') - this sentence can be implemented
- and the following sentence cannot be performed (sometimes it can be performed) - Sub_PARA is VARCHAR (8), the error message is a syntax error when converting from a string to DateTime. Select * from t_sub where item_local_code = '03004' and datediff (day, sub_para, getdate ()) = 29 and (SUB_DEL_FLAG <> 1)
- And when you can't execute, this statement will not return any record set Select * from t_subwhere item_local_code = '03004' and isdate (sub_para) = 0
-------------------------------------------------- ------------------------ - Cause, the index created in the table affects the order of the conditions - resulting in Datediff (Day, Sub_PARA, Getdate ())
- The following test illustrates this problem - Test Test Test Table and Data Create Table TB (item_local_code char (5), SUB_DEL_FLAG INT, SUB_PARA VARCHAR (10), Constraint PK_T PRIMARY Key (SUB_PARA, ITEM_LOCAL_CODE) INSERT TB SELECT '03004' , 1, '2003-1-1'Union all SELECT' 03005 ', 1,' 2003A1-1'go
- Query statement select * from (select * from tb where item_local_code = '03004' and sub_del_flag <> 0 and isdate (sub_para) = 1) a where datediff (day, sub_para, getdate ())> 29GO
- Delete DROP TABLE TB
/*--Test Results
Item_local_code sub_del_flag sub_para -------------------------- ---------- 03004 1 2003-1-1
Server: Message 241, Level 16, State 1, Row 3 When converting from a string to DateTime, a syntax error occurs.
- * /
