Incorrect syntax near the start_date

Question

Hello,

Could you please help me on the below query, its throwing error "err.Message = "ERROR [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '1/6/2021'.""

query = string.Format("INSERT INTO employees " +
             "(employee_id, employee_num, first_name, last_name, userid, dept_id, start_date, last_modified_date, active_ind, dept_head_ind, " +
             "email, address, phone, employee_level, adp_number, exempt_ind, employee_type, bb_num, bb_pin, exec_ind) " +
             "values({0}, {1}, '{2}', '{3}', '{4}', {5}, convert('{6}','yyyy/mm/dd',102), sysdate, 1, 0, '{7}', '{8}', null, 1, '{9}', 1, 1, null, null, {10})",
              count, newRow["employee_num"].ToString(), newRow["first_name"].ToString(), newRow["last_name"].ToString(), userid, newRow["dept_id"].ToString(), stdte,
              email, address, adpnum, (exec_ind ? "1" : "0"));

Answer 1

Hi @Raki ,

In SQL Server, we could use CONVERT with datetime data in different formats .

One is like below:

SELECT CONVERT(VARCHAR, GETDATE(), 102)    
--01/07/2021  

Please try with below replacement and check whether it is helpful to you.

Replace

convert('{6}','yyyy/mm/dd',102)  

with

convert(varchar,'{6}',101)  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Might try
yyyy-mm-dd in place of yyyy/mm/dd

Answer 3

Please try the following.

query = string.Format("INSERT INTO employees " +
              "(employee_id, employee_num, first_name, last_name, userid, dept_id, start_date, last_modified_date, active_ind, dept_head_ind, " +
              "email, address, phone, employee_level, adp_number, exempt_ind, employee_type, bb_num, bb_pin, exec_ind) " +
              "values({0}, {1}, '{2}', '{3}', '{4}', {5}, convert({6},'yyyy/mm/dd',102), sysdate, 1, 0, '{7}', '{8}', null, 1, '{9}', 1, 1, null, null, {10})",
               count, newRow["employee_num"].ToString(), newRow["first_name"].ToString(), newRow["last_name"].ToString(), userid, newRow["dept_id"].ToString(), stdte,
               email, address, adpnum, (exec_ind ? "1" : "0"));

Answer 4

Thanks for your reply. but still saying incorrect syntax error before the date. any help please.

query = string.Format("INSERT INTO employees " +
               "(employee_id, employee_num, first_name, last_name, userid, dept_id, start_date, last_modified_date, active_ind, dept_head_ind, " +
               "email, address, phone, employee_level, adp_number, exempt_ind, employee_type, bb_num, bb_pin, exec_ind) " +
               "values({0}, {1}, '{2}', '{3}', '{4}', {5}, convert({6},'yyyy/mm/dd',102), sysdate, 1, 0, '{7}', '{8}', null, 1, '{9}', 1, 1, null, null, {10})",
                count, newRow["employee_num"].ToString(), newRow["first_name"].ToString(), newRow["last_name"].ToString(), userid, newRow["dept_id"].ToString(), stdte,
                email, address, adpnum, (exec_ind ? "1" : "0"));

Answer 5

The format of the CONVERT function is CONVERT(datatype, {expression}, style) in T-SQL (SQL Server). The fact that you also have 'sysdate' in this query leads me to believe you are not actually connecting to a SQL Server system.

If you are connecting to SQL Server then replace sysdate with current_timestamp or getdate()