Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: 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 röda 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?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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:
    Code:
    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 röda 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_SCHEDULETMP
    Try cutting and pasting this to QA and see if it runs for you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •