December 13, 2012 at 12:33 pm
Hi,
I am trying to insert a record in datetime and datetime2 datatype columns.
ex: insert into table t1(creatdate) values(2012-03-09 12:34:45:567)
it throws error saying incorrect syntax. Can anyone please tell me how i need to enter the values.
Thanks
December 13, 2012 at 12:45 pm
Try this.
CREATE TABLE T1
(
CreateDate DateTime2
)
GO
INSERT INTO T1 (createdate)
VALUES ('2012-03-09 12:34:45:567')
GO
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 13, 2012 at 12:49 pm
Tried it as well.
CREATE TABLE T1
(
CreateDate DateTime2 not null
)
GO
INSERT INTO T1 (createdate)
VALUES ('2012-03-09 12:34:45:567')
GO
throws an error: cannot convert datetime to string
December 13, 2012 at 1:00 pm
I tested it and it works:
SELECT *
FROM T1 ;
CreateDate
2012-03-09 12:34:45.5670000
Are you using SQL Server 2012?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 13, 2012 at 1:08 pm
Working for me as well.
Thanks
December 13, 2012 at 2:11 pm
Only 'YYYYMMDD', rather than 'YYYY-MM-DD', is 100% safe:
INSERT INTO T1 (createdate)
VALUES ('20120309 12:34:45:567')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 13, 2012 at 2:46 pm
tanvishriya (12/13/2012)
Tried it as well.CREATE TABLE T1
(
CreateDate DateTime2 not null
)
GO
INSERT INTO T1 (createdate)
VALUES ('2012-03-09 12:34:45:567')
GO
throws an error: cannot convert datetime to string
Sure there's no other code anywhere? Nothing else in the batch, no trigger, no frontend code involved? Reason I ask is the error says 'converting datetime to string' and that conversion is not happening anywhere in the code you posted. There's a conversion string to datetime2, but none from datetime to string.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply