SQL Server - INSERT datetime field into database

Asked By karol1208 on 30-May-07 08:35 AM
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.




Dan Guzman replied on 30-May-07 08:43 AM
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 replied on 30-May-07 08:54 AM
this did not work too.
Dan Guzman replied on 30-May-07 09:11 AM
That should work.  What error message do you get?  Are you using Microsoft
SQL Server?

--
Hope this helps.

Dan Guzman
SQL Server MVP
Dan Guzman replied on 30-May-07 09:14 AM
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 replied on 30-May-07 09:21 AM
Yes.
Aaron Bertrand [SQL Server MVP] replied on 30-May-07 10:12 AM
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 replied on 30-May-07 12:41 PM
When I use the query linke this "INSERT INTO .... " ever thing is ok.
But when I use query :"exec sp_executesql ...." goes wrong.
Aaron Bertrand [SQL Server MVP] replied on 30-May-07 12:46 PM
Could you possibly show us THAT code?  Everything so far has been a wild
goose chase, because we are trying to spot errors in code you are not even
using.
karol1208 replied on 31-May-07 04:53 AM
On Wed, 30 May 2007 12:46:48 -0400, "Aaron Bertrand [SQL Server MVP]"


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 "?
Dan Guzman replied on 31-May-07 07:24 AM
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.


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
Amir Tabari replied on 24-Oct-07 12:11 PM
Hi.
Please Help Me to Insert Value Into a DateTime Field in T-SQL script
Tibor Karaszi replied on 24-Oct-07 01:00 PM
Not sure what you are asking, but here's an example on how you express a datetime value:

create table t(c1 int, c2 datetime)
insert into t values(1, '20071023 14:23:16')
SELECT * FROM t

Here's an elaboration about the datetime datatypes in SQL Server:
http://www.karaszi.com/SQLServer/info_datetime.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
altayeb hamed replied to karol1208 on 15-May-10 05:01 PM
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.
Dan Guzman replied to altayeb hamed on 16-May-10 01:13 AM
Try ISO 8601 format:

INSERT INTO ... VALUES('2007-05-29T00:00:00')

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
Pablo Bertrao replied to Dan Guzman on 22-Oct-10 12:30 PM
TRY NOT USING the  ''



for example,



CREATE TABLE(

id int,

date datetime)





INSERT INTO TABLE VALUES(10/10/2010)



if try like this



INSERT INTO TABLE VALUES ('10/10/2010') it will try to convert to varchar type
Mohammed Idris replied to karol1208 on 24-Nov-10 03:45 AM
11/23/2010 12:00:00 AM
Gwendolyn Barber replied to Dan Guzman on 29-Dec-10 06:22 PM
Hi Danny ,

I am taking a Sql RS class and I cannot figure out how to connect the values.  I need help right away or i will fail.  I will pay you.  Please contact me at 4075809777

or gwen149g@gmail.com
Gwendolyn Barber replied to Dan Guzman on 03-Jan-11 06:23 PM
yes

sql Rs

i am trying to do a subquery
Gwendolyn Barber replied to Dan Guzman on 03-Jan-11 06:25 PM
thank you,