Post reply

Error converting data type varchar to datetime in stored Procedure

  • January 3, 2012 at 12:14 am

    #391233

    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

  • January 3, 2012 at 12:43 am

    #1427565

    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

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • January 3, 2012 at 12:54 am

    #1427567

    ,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]

  • January 3, 2012 at 1:21 am

    #1427569

    Hello

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

  • January 3, 2012 at 1:28 am

    #1427572

    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.

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • January 3, 2012 at 1:36 am

    #1427573

    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]

  • January 3, 2012 at 5:39 am

    #1427654

    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

  • January 3, 2012 at 5:53 am

    #1427665

    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]

  • January 3, 2012 at 5:54 am

    #1427666

    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?

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • January 3, 2012 at 6:24 am

    #1427683

    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

  • January 3, 2012 at 10:18 am

    #1427867

    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

  • January 3, 2012 at 10:33 am

    #1427883

    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]

  • January 3, 2012 at 4:16 pm

    #1428034

    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'

  • January 5, 2012 at 5:13 am

    #1428921

    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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy