Sql Server Programming :: INSERT datetime field into database


	INSERT datetime field into database How can I insert value into field "datetime" type. I try like '2007-05-29 00:00:00' but I got "Error converting data type varchar to datatime.". I also try convert(datetime,'2007-05-29 00:00:00',112) but this did not work too. Consider using a datetime format that is independent of date format settings: '20070529 00:00:00' -- Hope this helps. Dan Guzman SQL Server MVP "karol1208" <karol1208(Cut this)@gazeta.pl> wrote in message news:pmrq53d7c4i9oojp46avtp7kg424hid1ap@4ax.com... - Hide quoted text - > How can I insert value into field "datetime" type. I try like > '2007-05-29 00:00:00' but I got "Error converting data > type varchar to datatime.". I also try convert(datetime,'2007-05-29 > 00:00:00',112) but this did not work too. On Wed, 30 May 2007 07:43:34 -0500, "Dan Guzman" <guzma@nospam-online.sbcglobal.net> wrote: this did not work too. - Hide quoted text - >Consider using a datetime format that is independent of date format >settings: '20070529 00:00:00' That should work. What error message do you get? Are you using Microsoft SQL Server? -- Hope this helps. Dan Guzman SQL Server MVP "karol1208" <karol1208(Cut this)@gazeta.pl> wrote in message news:gvsq535sr47i2249srvfqs3i39k1hegako@4ax.com... - Hide quoted text - > On Wed, 30 May 2007 07:43:34 -0500, "Dan Guzman" > <guzma@nospam-online.sbcglobal.net> wrote: > this did not work too. >>Consider using a datetime format that is independent of date format >>settings: '20070529 00:00:00' Also, do these work in your environment? SELECT CAST('20070529' AS datetime) SELECT CAST('20070529 00:00:00' AS datetime) -- Hope this helps. Dan Guzman SQL Server MVP "karol1208" <karol1208(Cut this)@gazeta.pl> wrote in message news:gvsq535sr47i2249srvfqs3i39k1hegako@4ax.com... - Hide quoted text - > On Wed, 30 May 2007 07:43:34 -0500, "Dan Guzman" > <guzma@nospam-online.sbcglobal.net> wrote: > this did not work too. >>Consider using a datetime format that is independent of date format >>settings: '20070529 00:00:00' On Wed, 30 May 2007 08:11:54 -0500, "Dan Guzman" <guzma@nospam-online.sbcglobal.net> wrote: >That should work. What error message do you get? "Error converting data type varchar to datatime." >Are you using Microsoft >SQL Server? Yes. -----------------------------------------------Reply----------------------------------------------- What happens when you try: '2007-05-05 00:00:00' '20070505 00:00:00' -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006 "karol1208" <karol1208(Cut this)@gazeta.pl> wrote in message news:pmrq53d7c4i9oojp46avtp7kg424hid1ap@4ax.com... - Hide quoted text - > How can I insert value into field "datetime" type. I try like > '2007-05-29 00:00:00' but I got "Error converting data > type varchar to datatime.". I also try convert(datetime,'2007-05-29 > 00:00:00',112) but this did not work too. On Wed, 30 May 2007 10:12:31 -0400, "Aaron Bertrand [SQL Server MVP]" <ten.@dnartreb.noraa> wrote: >What happens when you try: >'2007-05-05 00:00:00' >'20070505 00:00:00' When I use the query linke this "INSERT INTO .... " ever thing is ok. But when I use query :"exec sp_executesql ...." goes wrong. -----------------------------------------------Reply----------------------------------------------- > But when I use query :"exec sp_executesql ...." goes wrong. Could you possibly show us THAT code? Everything so far has been a wild goose chase, because we're trying to spot errors in code you're not even using. -----------------------------------------------Reply----------------------------------------------- On Wed, 30 May 2007 12:46:48 -0400, "Aaron Bertrand [SQL Server MVP]" <ten.@dnartreb.noraa> wrote: >> But when I use query :"exec sp_executesql ...." goes wrong. >Could you possibly show us THAT code? Everything so far has been a wild >goose chase, because we're trying to spot errors in code you're not even >using. exec sp_executesql N'INSERT INTO doc ( doc_Id,dok_Typ,coc_Date) VALUES (@P1,@P2)', N' @P1 int,@P2 datetime', 12,'2007-05-29 00:00:00' this query returns error:"Error converting data type varchar to datatime.". But this INSERT INTO doc ( doc_Id,dok_Typ,coc_Date) VALUES (12,'2007-05-29 00:00:00') go ok. Is there a difference to use "exec sp_executesql "? -----------------------------------------------Reply----------------------------------------------- > this query returns error:"Error converting data > type varchar to datatime.". When I these queries, I get the error "There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.". This is expected because only 2 values are specified although 3 columns are specified in the column list. > go ok. Is there a difference to use "exec sp_executesql "? No, the interpretation of data strings is the same. Please post code that actually reproduces the error, including CREATE TABLE statements. This will better help us identify the reason for your error. -- Hope this helps. Dan Guzman SQL Server MVP "karol1208" <karol1208(Cut this)@gazeta.pl> wrote in message news:b33t535q6lm4ira2ocot9oqcbrdnnveajs@4ax.com... - Hide quoted text - > On Wed, 30 May 2007 12:46:48 -0400, "Aaron Bertrand [SQL Server MVP]" > <ten.@dnartreb.noraa> wrote: >>> But when I use query :"exec sp_executesql ...." goes wrong. >>Could you possibly show us THAT code? Everything so far has been a wild >>goose chase, because we're trying to spot errors in code you're not even >>using. > exec sp_executesql N'INSERT INTO doc ( > doc_Id,dok_Typ,coc_Date) > VALUES > (@P1,@P2)', > N' @P1 int,@P2 datetime', > 12,'2007-05-29 00:00:00' > this query returns error:"Error converting data > type varchar to datatime.". > But this > INSERT INTO doc ( doc_Id,dok_Typ,coc_Date) > VALUES (12,'2007-05-29 00:00:00') > go ok. Is there a difference to use "exec sp_executesql "?