Converting Datetime error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting Datetime error

Author

Topic

CleverRainbow
Starting Member

3 Posts

Posted - 2012-06-01 : 00:27:49

This is really bugging me, I keep receiving this error: 'Conversion failed when converting datetime from character string.'

SET DATEFORMAT dmy
INSERT INTO Member
VALUES (1000019,'Evan','Bevan','7/1/1991 0:00:00','12 Green St.','Gold Coast','Queensland',4217,'(07)55734516',0413-111112,'75896C',8/7/2000 0:00:00,0,1,'aaaaas')
GO

This is how i've created the table. The first date is DOB, the second is join date in that order. I believe the error has something to do with 'DEFAULT GETDATE()'?

CREATE TABLE Member (
DOB DATETIME NOT NULL,
JoinDate DATETIME DEFAULT GETDATE(),

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 00:40:20

always try to pass date values in unambiguos iso format

see

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 00:41:48

Also your values are not in same order as your column definition. in that case add an explicit column list in INSERT


INSERT INTO Member (Field1Name,Field2Name,...)
VALUES (1000019,'Evan','Bevan','7/1/1991 0:00:00','12 Green St.','Gold Coast','Queensland',4217,'(07)55734516',0413-111112,'75896C',8/7/2000 0:00:00,0,1,'aaaaas')
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CleverRainbow
Starting Member

3 Posts

Posted - 2012-06-01 : 01:02:43

Ah, thank you very much! I added a column list and it worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources