Error converting data type varchar to datetime in stored Procedure

  • archana 2530

    SSC-Addicted

    Points: 479

    ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]

    @LNK_File_ID numeric(18, 0) = NULL,

    @LNK_Client_ID numeric(18, 0) = NULL,

    @ExpanseDate datetime = NULL

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT E.[ExpanseId]

    ,CONVERT(Datetime,E.[ExpanseDate],103) as [ExpanseDate]

    ,E.[Amount]

    ,E.[LNK_File_ID]

    ,E.[LNK_SubFile_ID]

    ,CONVERT(Datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]

    ,E.[CreatedOn]

    ,c.Client_FirstName as ClientName

    ,f.FileNo

    ,f.FileName

    –,sf.SubFileNo

    –,sf.SubFileName

    FROM [AFMS].[dbo].[Expanse] E

    JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]

    JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]

    JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]

    Where

    E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID

    Else E.LNK_File_ID End and

    E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID

    Else E.[LNK_CreatedBy_ID] End and

    E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate

    Else E.[ExpanseDate] End

    End

    I am Executing this Stored Procedure

    exec [SelectAll_ExpenseDetails]

    @LNK_File_ID = null,

    @LNK_Client_ID = null,

    @ExpanseDate = ’29/12/2011 12:00:00 AM’

    but I am getting Error

    Msg 8114, Level 16, State 5, Procedure SelectAll_ExpenseDetails, Line 0

    Error converting data type varchar to datetime.

    Archana Mistry

    Sr. Programmer

    Kintudesigns.com

  • Paul White

    SSC Guru

    Points: 150316

    Have you tried using a format that is not DATEFORMAT dependent?

    The string ’29/12/2011 12:00:00 AM’ is probably being interpreted as the 12th day of the 29th month 🙂

    For example:

    ‘2011-12-29T00:00:00’

    ‘2011-12-29’

    ‘20111229’

    See http://msdn.microsoft.com/en-us/library/ms180878.aspx#StringLiteralDateandTimeFormats

  • ChrisM@home

    SSC-Insane

    Points: 24260

    ,CONVERT(Datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]

    E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID

    Else E.[LNK_CreatedBy_ID] End and

    @LNK_Client_ID numeric(18, 0) = NULL,


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • archana 2530

    SSC-Addicted

    Points: 479

    Hello

    I had tried out with 3 of the options.. but still getting Same error.

  • Paul White

    SSC Guru

    Points: 150316

    archana 2530 (1/3/2012)


    I had tried out with 3 of the options.. but still getting Same error.

    Ok so it’s not that then. Have a look at the code lines Chris highlighted. I must admit I didn’t get as far as looking at the body of the procedure.

  • ChrisM@home

    SSC-Insane

    Points: 24260

    What datatype is E.[ExpanseDate]?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • archana 2530

    SSC-Addicted

    Points: 479

    Create Table of Expense

    USE [AFMS]

    GO

    /****** Object: Table [dbo].[Expanse] Script Date: 01/03/2012 18:03:30 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Expanse](

    [ExpanseId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

    [ExpanseDate] [datetime] NOT NULL,

    [Amount] [numeric](18, 0) NOT NULL,

    [LNK_File_ID] [numeric](18, 0) NOT NULL,

    [LNK_SubFile_ID] [numeric](18, 0) NULL,

    [LNK_CreatedBy_ID] [numeric](18, 0) NOT NULL,

    [CreatedOn] [datetime] NOT NULL,

    CONSTRAINT [PK_Expanse] PRIMARY KEY CLUSTERED

    (

    [ExpanseId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    Add Data

    — —————————————————————–

    — This script has been automatically generated by SQL Scripter 3.03

    — (unregistered and limited version)

    http://www.sqlscripter.com

    — 3/1/2012 5:58:34 PM

    — 7 records

    — —————————————————————–

    SET IDENTITY_INSERT [dbo].[Expanse] ON

    IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 1)

    INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

    VALUES (1, ‘20111230’, 100, 1, 4, 4, ‘20111230’)

    ELSE

    UPDATE [dbo].[Expanse] SET [ExpanseDate] = ‘20111230’, [Amount] = 100, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = ‘20111230’ WHERE [ExpanseId] = 1

    IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 2)

    INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

    VALUES (2, ‘20111230’, 100, 1, 4, 4, ‘20111230’)

    ELSE

    UPDATE [dbo].[Expanse] SET [ExpanseDate] = ‘20111230’, [Amount] = 100, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = ‘20111230’ WHERE [ExpanseId] = 2

    IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 3)

    INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

    VALUES (3, ‘20111230’, 111, 2, NULL, 4, ‘20111230’)

    ELSE

    UPDATE [dbo].[Expanse] SET [ExpanseDate] = ‘20111230’, [Amount] = 111, [LNK_File_ID] = 2, [LNK_SubFile_ID] = NULL, [LNK_CreatedBy_ID] = 4, [CreatedOn] = ‘20111230’ WHERE [ExpanseId] = 3

    IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 6)

    INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

    VALUES (6, ‘20111229’, 110, 1, NULL, 4, ‘20111229’)

    ELSE

    UPDATE [dbo].[Expanse] SET [ExpanseDate] = ‘20111229’, [Amount] = 110, [LNK_File_ID] = 1, [LNK_SubFile_ID] = NULL, [LNK_CreatedBy_ID] = 4, [CreatedOn] = ‘20111229’ WHERE [ExpanseId] = 6

    IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 7)

    INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

    VALUES (7, ‘20111229’, 15, 1, 4, 4, ‘20111229’)

    ELSE

    UPDATE [dbo].[Expanse] SET [ExpanseDate] = ‘20111229’, [Amount] = 15, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = ‘20111229’ WHERE [ExpanseId] = 7

    IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 8)

    INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

    VALUES (8, ‘20111228’, 19, 1, 5, 4, ‘20111228’)

    ELSE

    UPDATE [dbo].[Expanse] SET [ExpanseDate] = ‘20111228’, [Amount] = 19, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 5, [LNK_CreatedBy_ID] = 4, [CreatedOn] = ‘20111228’ WHERE [ExpanseId] = 8

    IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 9)

    INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

    VALUES (9, ‘20111230’, 15, 3, 6, 4, ‘20111230’)

    ELSE

    UPDATE [dbo].[Expanse] SET [ExpanseDate] = ‘20111230’, [Amount] = 15, [LNK_File_ID] = 3, [LNK_SubFile_ID] = 6, [LNK_CreatedBy_ID] = 4, [CreatedOn] = ‘20111230’ WHERE [ExpanseId] = 9

    SET IDENTITY_INSERT [dbo].[Expanse] OFF

    now my Stored Procedure

    USE [AFMS]

    GO

    /****** Object: StoredProcedure [dbo].[SelectAll_ExpenseDetails] Script Date: 01/03/2012 18:06:40 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    — =============================================

    — Author: Created By Archana

    — Create date: 09-12-2011

    — Description: <Description,,>

    — =============================================

    ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]

    @LNK_File_ID numeric(18, 0) = NULL,

    –@LNK_SubFile_ID numeric(18, 0) = NULL,

    @LNK_Client_ID numeric(18, 0) = NULL,

    @ExpanseDate datetime = NULL

    — varchar(max)

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT E.[ExpanseId]

    ,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]

    ,E.[Amount]

    ,E.[LNK_File_ID]

    ,E.[LNK_SubFile_ID]

    ,CONVERT(datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]

    ,E.[CreatedOn]

    ,c.Client_FirstName as ClientName

    ,f.FileNo

    ,f.FileName

    –,sf.SubFileNo

    –,sf.SubFileName

    FROM [AFMS].[dbo].[Expanse] E

    JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]

    JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]

    JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]

    Where

    E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID

    Else E.LNK_File_ID End and

    –E.LNK_SubFile_ID = Case when @LNK_SubFile_ID IS NOT NULL THEN @LNK_SubFile_ID

    — Else E.LNK_SubFile_ID End and

    E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID

    Else E.[LNK_CreatedBy_ID] End and

    E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate

    Else E.[ExpanseDate] End

    End

    exec [SelectAll_ExpenseDetails]

    @LNK_File_ID = null,

    @LNK_Client_ID = null,

    @ExpanseDate = ’29/12/2011 12:00:00 AM’

    I am Executing this Stored Procedure

    exec [SelectAll_ExpenseDetails]

    @LNK_File_ID = null,

    @LNK_Client_ID = null,

    @ExpanseDate = ’29/12/2011 12:00:00 AM’

    but I am getting Error

    Msg 8114, Level 16, State 5, Procedure SelectAll_ExpenseDetails, Line 0

    Error converting data type varchar to datetime.

    Archana Mistry

    Sr. Programmer

    Kintudesigns.com

  • ChrisM@home

    SSC-Insane

    Points: 24260

    Run the query outside the stored procedure – always much easier to debug – and chop out some unnecessary bits.

    DECLARE

    @LNK_File_ID numeric(18, 0),

    @LNK_Client_ID numeric(18, 0),

    @ExpanseDate datetime

    SET @ExpanseDate = ’29/12/2011 12:00:00 AM’

    SELECT E.[ExpanseId]

    ,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]

    ,E.[Amount]

    ,E.[LNK_File_ID]

    ,E.[LNK_SubFile_ID]

    ,CONVERT(datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]

    ,E.[CreatedOn]

    ,c.Client_FirstName as ClientName

    ,f.FileNo

    ,f.FileName

    –,sf.SubFileNo

    –,sf.SubFileName

    FROM [AFMS].[dbo].[Expanse] E

    JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]

    JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]

    JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]

    Where

    /*

    E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID

    Else E.LNK_File_ID End and

    –E.LNK_SubFile_ID = Case when @LNK_SubFile_ID IS NOT NULL THEN @LNK_SubFile_ID

    — Else E.LNK_SubFile_ID End and

    E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID

    Else E.[LNK_CreatedBy_ID] End and

    */

    E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate

    Else E.[ExpanseDate] End

    What happens when you run this?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Paul White

    SSC Guru

    Points: 150316

    Thanks, that’s very helpful. As Chris noted earlier, the [LNK_CreatedBy_ID] column is defined as [numeric](18, 0) NOT NULL, but the procedure contains CONVERT(datetime,E.[b]LNK_CreatedBy_ID[/b],103) as [LNK_CreatedBy_ID]. Are you intending to try to convert this number to a date/time using style 103?

  • sami.sqldba

    Ten Centuries

    Points: 1168

    Hi dear

    Try to put varchar(20) inside the convert function rather then datetime.

    Thanks & Regards
    Syed Sami Ur Rehman
    SQL-Server (Developer)
    Hyderabad
    Email-sami.sqldba@gmail.com

  • archana 2530

    SSC-Addicted

    Points: 479

    Hii

    ChrisM@home Thanks alot for helping me..

    and

    SQL Kiwi its my mistake u consider as ChrisM@home changes

    Archana Mistry

    Sr. Programmer

    Kintudesigns.com

  • ChrisM@home

    SSC-Insane

    Points: 24260

    You’re welcome.

    Did you resolve the problem? Would you like to share with us the resolution?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Edward Shaw

    SSC Eights!

    Points: 896

    Archana 2530. Not sure if this helps but I did what sami.sqldba suggested and changed parameter to varchar(25) then added one more statement to your stored proc that converts the date. When I created your table and commented out the joins where I didn’t have the tables available I was able to get this to run. It returned the correct two records (those created on 12/29/2011).

    USE [AFMS]

    GO

    /****** Object: StoredProcedure [dbo].[SelectAll_ExpenseDetails] Script Date: 01/03/2012 18:06:40 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    — =============================================

    — Author: Created By Archana

    — Create date: 09-12-2011

    — Description: <Description,,>

    — =============================================

    ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]

    @LNK_File_ID numeric(18, 0) = NULL,

    –@LNK_SubFile_ID numeric(18, 0) = NULL,

    @LNK_Client_ID numeric(18, 0) = NULL,

    @ExpanseDate varchar(25) = NULL — Changed

    — varchar(max)

    AS

    BEGIN

    SET NOCOUNT ON;

    SET @ExpanseDate = CONVERT(DATETIME, @ExpanseDate, 103) — Added

    SELECT E.[ExpanseId]

    ,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]

    ,E.[Amount]

    ,E.[LNK_File_ID]

    ,E.[LNK_SubFile_ID]

    ,CONVERT(datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]

    ,E.[CreatedOn]

    ,c.Client_FirstName as ClientName

    ,f.FileNo

    ,f.FileName

    –,sf.SubFileNo

    –,sf.SubFileName

    FROM [AFMS].[dbo].[Expanse] E

    JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]

    JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]

    JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]

    Where

    E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID

    Else E.LNK_File_ID End and

    –E.LNK_SubFile_ID = Case when @LNK_SubFile_ID IS NOT NULL THEN @LNK_SubFile_ID

    — Else E.LNK_SubFile_ID End and

    E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID

    Else E.[LNK_CreatedBy_ID] End and

    E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate

    Else E.[ExpanseDate] End

    End

    exec [SelectAll_ExpenseDetails]

    @LNK_File_ID = null,

    @LNK_Client_ID = null,

    @ExpanseDate = ’29/12/2011 12:00:00 AM’

  • archana 2530

    SSC-Addicted

    Points: 479

    Hi

    I have still Problem same.

    in stored Procedure Still same Error come.. When I am Executing Stored Procedure

    my Actual Procedure is

    USE [AFMS]

    GO

    /****** Object: StoredProcedure [dbo].[SelectAll_ExpenseDetails] Script Date: 01/05/2012 17:37:59 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    — =============================================

    — Author: Created By Archana

    — Create date: 09-12-2011

    — Description: <Description,,>

    — =============================================

    ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]

    @LNK_File_ID numeric(18, 0) = NULL,

    @LNK_Client_ID numeric(18, 0) = NULL,

    @ExpanseDate datetime = NULL

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT E.[ExpanseId]

    ,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]

    ,E.[Amount]

    ,E.[LNK_File_ID]

    ,E.[LNK_SubFile_ID]

    ,E.[LNK_CreatedBy_ID]

    ,CONVERT(datetime,E.[CreatedOn],103) as [CreatedOn]

    ,c.Client_FirstName as ClientName

    ,f.FileNo

    ,f.FileName

    FROM [AFMS].[dbo].[Expanse] E

    JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]

    JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]

    JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]

    Where

    E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID

    Else E.LNK_File_ID End and

    E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID

    Else E.[LNK_CreatedBy_ID] End and

    E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate

    Else E.[ExpanseDate] End

    End

    Execute Procedure

    exec [SelectAll_ExpenseDetails]

    @LNK_File_ID = null,

    @LNK_Client_ID = null,

    @ExpanseDate = ’29/12/2011′

    Error is

    Msg 8114, Level 16, State 5, Procedure SelectAll_ExpenseDetails, Line 0

    Error converting data type varchar to datetime.

    Archana Mistry

    Sr. Programmer

    Kintudesigns.com

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply