following data and I get the subsequent message.
INSERT INTO t_Answer ( AnswerID, AssessmentID, QuestionID, EvaluationValue,
AnswerText, CreationUser_ID, RevisionUser_ID )
VALUES ( 457 , 130, 4, '14', 'testing what I want to do better', 152, 152 )
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
As you can see, I'm not entering any datetime data. I do, however, have two
fields that are date time fields.
I don't know what to do next so that i can enter data in the database.
I've tried to enter info into another table and got the same message.
Thanks for your help,
KarolusHi
At http://www.aspfaq.com/etiquette.asp?id=5006 there is instructions to post
DDL which would tell us what datatypes your columns are.
Check if there are any triggers on this table or invalid defaults e.g.
CREATE TABLE MyDate ( ID INT, ADATE datetime NOT NULL DEFAULT 'Help' )
INSERT INTO MyDate (id) values (1)
gives your error.
John
"Karolus" wrote:
> I'm a SQL newbie and am totally perplexed. I have tried to enter the
> following data and I get the subsequent message.
> INSERT INTO t_Answer ( AnswerID, AssessmentID, QuestionID, EvaluationValue
,
> AnswerText, CreationUser_ID, RevisionUser_ID )
> VALUES ( 457 , 130, 4, '14', 'testing what I want to do better', 152, 152
)
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> As you can see, I'm not entering any datetime data. I do, however, have t
wo
> fields that are date time fields.
> I don't know what to do next so that i can enter data in the database.
> I've tried to enter info into another table and got the same message.
> Thanks for your help,
> Karolus|||Thanks, John, I got the document. Here is the table definition. (Thanks fo
r
the advice.)
CREATE TABLE [t_Answer] (
[AnswerID] [CodeID] NOT NULL ,
[AssessmentID] [CodeID] NOT NULL ,
[QuestionID] [CodeID] NOT NULL ,
[EvaluationValue] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AnswerText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatusID] [RecordStatusID] NOT NULL ,
[RecordID] [RecordID] IDENTITY (1, 1) NOT NULL ,
[CreationUser_ID] [KeyForeign] NOT NULL ,
[CreationDtm] [DateTmCreated] NOT NULL ,
[RevisionUser_ID] [KeyForeign] NOT NULL ,
[RevisionDtm] [DateTimeRequired] NOT NULL ,
CONSTRAINT [COMMENTID] PRIMARY KEY CLUSTERED
(
[AnswerID],
[AssessmentID],
[QuestionID]
) ON [PRIMARY] ,
CONSTRAINT [fk_Answer_AssessmentID_Assessment] FOREIGN KEY
(
[AssessmentID]
) REFERENCES [t_Assessment] (
[AssessmentID]
),
CONSTRAINT [fk_Answer_QuestionID_QuestionID] FOREIGN KEY
(
[QuestionID]
) REFERENCES [t_QuestionID] (
[QuestionID]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I hope this information is helpful. I don't understand what I'm screwing up
.
"John Bell" wrote:
> Hi
> At http://www.aspfaq.com/etiquette.asp?id=5006 there is instructions to po
st
> DDL which would tell us what datatypes your columns are.
> Check if there are any triggers on this table or invalid defaults e.g.
> CREATE TABLE MyDate ( ID INT, ADATE datetime NOT NULL DEFAULT 'Help' )
> INSERT INTO MyDate (id) values (1)
> gives your error.
> John
> "Karolus" wrote:
>|||Please supply the definitions of the user-defined types: CodeID,
RecordStatusID, RecordID, KeyForeign, DateTmCreated, and DateTimeRequired.
I suspect the problem is to be found there, or in a default attached to a
user defined type.
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:B32CFD3E-BF21-471D-8E32-F33B5E616DE1@.microsoft.com...
> Thanks, John, I got the document. Here is the table definition. (Thanks
for
> the advice.)
> CREATE TABLE [t_Answer] (
> [AnswerID] [CodeID] NOT NULL ,
> [AssessmentID] [CodeID] NOT NULL ,
> [QuestionID] [CodeID] NOT NULL ,
> [EvaluationValue] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AnswerText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatusID] [RecordStatusID] NOT NULL ,
> [RecordID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> [CreationUser_ID] [KeyForeign] NOT NULL ,
> [CreationDtm] [DateTmCreated] NOT NULL ,
> [RevisionUser_ID] [KeyForeign] NOT NULL ,
> [RevisionDtm] [DateTimeRequired] NOT NULL ,
> CONSTRAINT [COMMENTID] PRIMARY KEY CLUSTERED
> (
> [AnswerID],
> [AssessmentID],
> [QuestionID]
> ) ON [PRIMARY] ,
> CONSTRAINT [fk_Answer_AssessmentID_Assessment] FOREIGN KEY
> (
> [AssessmentID]
> ) REFERENCES [t_Assessment] (
> [AssessmentID]
> ),
> CONSTRAINT [fk_Answer_QuestionID_QuestionID] FOREIGN KEY
> (
> [QuestionID]
> ) REFERENCES [t_QuestionID] (
> [QuestionID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> I hope this information is helpful. I don't understand what I'm screwing
up.
> "John Bell" wrote:
>
post
EvaluationValue,
152 )
have two|||Hi Karolus,
I agree with John. You may need to check triggers entering values into
RevisionDtm and CreationDtm.
You have four not null fields for that you didn't provide values in
your insert statement,
so you may have triggers for them.
I hope it help you.
Kim|||Hi
CreationDtm and Revisiondtm are not nullable, but you have not supplied a
value in your statement, therefore you would get an error for that but
normally not the one you have stated, therefore I think you may have default
s
in the user defined types DateTimeRequired and DateTmCreated that are
incorrect.
John
"Karolus" wrote:
> Thanks, John, I got the document. Here is the table definition. (Thanks
for
> the advice.)
> CREATE TABLE [t_Answer] (
> [AnswerID] [CodeID] NOT NULL ,
> [AssessmentID] [CodeID] NOT NULL ,
> [QuestionID] [CodeID] NOT NULL ,
> [EvaluationValue] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AnswerText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatusID] [RecordStatusID] NOT NULL ,
> [RecordID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> [CreationUser_ID] [KeyForeign] NOT NULL ,
> [CreationDtm] [DateTmCreated] NOT NULL ,
> [RevisionUser_ID] [KeyForeign] NOT NULL ,
> [RevisionDtm] [DateTimeRequired] NOT NULL ,
> CONSTRAINT [COMMENTID] PRIMARY KEY CLUSTERED
> (
> [AnswerID],
> [AssessmentID],
> [QuestionID]
> ) ON [PRIMARY] ,
> CONSTRAINT [fk_Answer_AssessmentID_Assessment] FOREIGN KEY
> (
> [AssessmentID]
> ) REFERENCES [t_Assessment] (
> [AssessmentID]
> ),
> CONSTRAINT [fk_Answer_QuestionID_QuestionID] FOREIGN KEY
> (
> [QuestionID]
> ) REFERENCES [t_QuestionID] (
> [QuestionID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> I hope this information is helpful. I don't understand what I'm screwing
up.
> "John Bell" wrote:
>|||Thanks all for your help. I VERY much appreciate it. I've come for Sybase
and am overwhelmed by the difference. Here are the domain definitions and
the code that created the table, t_answers. I assumed that I should provide
a datetime, but that didn't work either...so I'm
.Here's the information:
/ *=======================================
=======================*/
/* Domain: DateTmCreated */
/ *=======================================
=======================*/
sp_addtype DateTmCreated, 'datetime' , 'not null'
go
/ *=======================================
=======================*/
/* Domain: DateTimeNotMandatory */
/ *=======================================
=======================*/
sp_addtype DateTimeNotMandatory, 'datetime'
go
/ *=======================================
=======================*/
/* Domain: DateTimeRequired */
/ *=======================================
=======================*/
sp_addtype DateTimeRequired, 'datetime' , 'not null'
go
create default D_DateTimeRequired
as 'CURRENT TIMESTAMP'
go
sp_bindefault D_DateTimeRequired, DateTimeRequired
go
/ *=======================================
=======================*/
/* Domain: DateTm */
/ *=======================================
=======================*/
sp_addtype DateTm, 'datetime'
go
create default D_DateTm
as 'CURRENT TIMESTAMP'
go
sp_bindefault D_DateTm, DateTm
go
/ *=======================================
=======================*/
/* Table : t_Answer */
/ *=======================================
=======================*/
create table t_Answer (
AnswerID CodeID not null,
AssessmentID CodeID not null,
QuestionID CodeID not null,
EvaluationValue text null,
AnswerText text null,
RecordStatusID RecordStatusID not null,
RecordID RecordID identity,
CreationUser_ID KeyForeign not null,
CreationDtm DateTmCreated not null,
RevisionUser_ID KeyForeign not null,
RevisionDtm DateTimeRequired not null,
constraint COMMENTID primary key (AnswerID, AssessmentID, QuestionID)
)
go
"John Bell" wrote:
> Hi
> CreationDtm and Revisiondtm are not nullable, but you have not supplied a
> value in your statement, therefore you would get an error for that but
> normally not the one you have stated, therefore I think you may have defau
lts
> in the user defined types DateTimeRequired and DateTmCreated that are
> incorrect.
> John
> "Karolus" wrote:
>|||Looks like the error is just the CREATE DEFAULT statements.
CURRENT_TIMESTAMP is a keyword and mustn't be quoted as a string:
CREATE DEFAULT D_DateTm
AS CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--sql
No comments:
Post a Comment