Sunday, March 11, 2012

Error converting data type varchar to datetime

An application i am using creates an error.

Using SQL-profiler i catch this statement:

exec sp_executesql N'insert into RECORDING_SCHEDULE (programme_oid, capture_source_oid, filename, status, recording_group, recording_type, manual_channel_oid,
manual_start_time, manual_end_time, quality_level, pre_pad_minutes, post_pad_minutes) values (@.P1,@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10,@.P11,@.P1 2)',N'@.P1 int,@.P2 int,@.P3
nvarchar(19),@.P4 int,@.P5 int,@.P6 int,@.P7 int,@.P8 datetime,@.P9 datetime,@.P10 int,@.P11 int,@.P12 int',177748,2,N'Lilla rda traktorn',4,40,5,16,''2006-06-21
17:00:00:000'',''2006-06-21 20:00:00:000'',1,1,2

After removing the double ' around the date and time parts i get the following error when running in QA:
Msg 8114, Level 16, State 5, Line 0
Error converting data type varchar to datetime.

If a remove the time portion it works.

The DDL for the table is:

CREATE TABLE [dbo].[RECORDING_SCHEDULE](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL CONSTRAINT [DF__RECORDING__quali__38996AB5] DEFAULT ((0)),
[pre_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__pre_p__398D8EEE] DEFAULT ((0)),
[post_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__post___3A81B327] DEFAULT ((0)),
CONSTRAINT [aaaaaRECORDING_SCHEDULE_PK] PRIMARY KEY NONCLUSTERED

What am I missing?Well, I corrected a type where you listed [@.P1 2] rather than the obviously intended [@.P12], but after that this code worked fine for me:CREATE TABLE [dbo].[RECORDING_SCHEDULETMP](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL,
[pre_pad_minutes] [int] NULL,
[post_pad_minutes] [int] NULL,
)

exec sp_executesql N'
insert into RECORDING_SCHEDULETMP
(programme_oid,
capture_source_oid,
filename,
status,
recording_group,
recording_type,
manual_channel_oid,
manual_start_time,
manual_end_time,
quality_level,
pre_pad_minutes,
post_pad_minutes)
values (@.P1,
@.P2,
@.P3,
@.P4,
@.P5,
@.P6,
@.P7,
@.P8,
@.P9,
@.P10,
@.P11,
@.P12)',
N'@.P1 int,
@.P2 int,
@.P3 nvarchar(19),
@.P4 int,
@.P5 int,
@.P6 int,
@.P7 int,
@.P8 datetime,
@.P9 datetime,
@.P10 int,
@.P11 int,
@.P12 int',
177748,
2,
N'Lilla rda traktorn',
4,
40,
5,
16,
'2006-06-21 17:00:00:000',
'2006-06-21 20:00:00:000',
1,
1,
2

drop table RECORDING_SCHEDULETMPTry cutting and pasting this to QA and see if it runs for you.|||Oh, I think I see the issue. You "removed" the double quotes, when you should have just changed them to single quotes, as in my sample code...

No comments:

Post a Comment