Getting a Date Conversion Error When Executing Stored Procedure

  • This seems like it should be simple to solve, but for some reason I can't find the answer.

    We have a simple stored procedure that has one date parameter that we are trying to execute. When we try to execute the procedure while passing the date to the procedure, we receive the following error:

    "Conversion failed when converting date and/or time from character string."

    Any ideas how to fix it? See stored procedure and execution below.

    Here's the stored procedure:

    ALTER PROCEDURE [dbo].[MyStoredProcedure]

    @MyDate date = ''

    AS

    BEGIN

    If @MyDate = '' OR @MyDate = '*' SET @MyDate = GetDate()

    INSERT INTO MyTable1 ([name], [MyDate])

    SELECT [name], @MyDate AS Expr1

    FROM MyTable2

    END

    And, here's the call (along with some of my alternate attempts) to the procedure that is causing the error

    -- My first attempt was to explicitly try entering the date.

    execute [MyProcedure] '2016-12-23'

    -- Then I tried to convert it

    declare @CurDate date = convert(date, '2016-12-23')

    execute [MyProcedure] @CurDate

    /*

    Note also I tried other variations of my date format including:

    '2016-12-23 00:00:00'

    '2016-12-23 00:00:00.000'

    '12232016'

    '12/23/2016'

    ... and others

    */

  • ptownbro (12/23/2016)


    This seems like it should be simple to solve, but for some reason I can't find the answer.

    We have a simple stored procedure that has one date parameter that we are trying to execute. When we try to execute the procedure while passing the date to the procedure, we receive the following error:

    "Conversion failed when converting date and/or time from character string."

    Any ideas how to fix it? See stored procedure and execution below.

    Here's the stored procedure:

    ALTER PROCEDURE [dbo].[MyStoredProcedure]

    @MyDate date = ''

    AS

    BEGIN

    If @MyDate = '' OR @MyDate = '*' SET @MyDate = GetDate()

    INSERT INTO MyTable1 ([name], [MyDate])

    SELECT [name], @MyDate AS Expr1

    FROM MyTable2

    END

    And, here's the call (along with some of my alternate attempts) to the procedure that is causing the error

    -- My first attempt was to explicitly try entering the date.

    execute [MyProcedure] '2016-12-23'

    -- Then I tried to convert it

    declare @CurDate date = convert(date, '2016-12-23')

    execute [MyProcedure] @CurDate

    /*

    Note also I tried other variations of my date format including:

    '2016-12-23 00:00:00'

    '2016-12-23 00:00:00.000'

    '12232016'

    '12/23/2016'

    ... and others

    */

    You are missing the style parameter of the convert function

    😎

    convert(date, '2016-12-23',126) --ISO8601 yyyy-mm-ddThh:mi:ss.mmm

  • Strange! I've never seen anyone set a date datatype variable to an empty string. But if you do that, it immediately gets set to Jan 1, 1900. The test for the parameter = '' will never be true.

    DECLARE @d date;

    SET @d = '';

    SELECT @d;

  • Thanks for the response. Tried to add the style and it is still giving me the same answer.

  • Thanks for the suggestion. This is actually something developed by someone else, but thanks just the same. However, this isn't what is causing my original problem. Any ideas how to fix that?

  • Here is a better way of testing for the missing date value

    😎

    ALTER PROCEDURE [dbo].[MyStoredProcedure]

    (

    @MyDate date = NULL

    )

    AS

    BEGIN

    If @MyDate IS NULL

    BEGIN

    SET @MyDate = CONVERT(DATE,GETDATE(),0);

    END

    INSERT INTO MyTable1 ([name], [MyDate])

    SELECT [name], @MyDate

    FROM MyTable2;

    END

  • Bill Talada (12/23/2016)


    Strange! I've never seen anyone set a date datatype variable to an empty string. But if you do that, it immediately gets set to Jan 1, 1900. The test for the parameter = '' will never be true.

    DECLARE @d date;

    SET @d = '';

    SELECT @d;

    The missing date value check in OP's code isn't elegant but it works, implicit conversion.

    😎

    Obviously the test for the asterisk symbol doesn't do anything as that will never be true, not even on the 42 day of the year 1900.

  • I always use a ccyymmdd format such as '20161223' in all my scripts because it works regardless of many date settings. But, I suspect you have something else going on unrelated to the proc parameter, perhaps with the table you are inserting into, or perhaps you left something critical out of your post. Sometimes, a default schema other than dbo can really confuse a developer. We need more actual details before we can offer additional help.

  • Ok. I think I see what you are saying now.

    Correct me if I'm wrong... you're saying that the error is being generated from the IF clause portion of the script.

    I'll try your suggestion and report back.

    Btw... I also already tried '20161223' format and that didn't work either.

  • In your version - you have '' as the default value which is not a valid date. An empty string as a date is implicitly converted to a 0 which will be 1900-01-01.

    In your check, you then are trying to check for either a blank (empty string) or '*' - and that '*' is not a valid date and cannot be converted to a valid date.

    Declare @MyDate date = '*';

    Select @MyDate;

    This will cause the same error you are seeing...

    Now, if you want to be able to pass in a string value you need to change the input parameter and define another variable and insure that the caller always sends the date in a specific format (e.g. YYYYMMDD):

    ALTER PROCEDURE [dbo].[MyStoredProcedure]

    @MyDate char(8) = ''

    AS

    BEGIN

    DECLARE @inputDate date = coalesce(nullif(@MyDate, '*'), nullif(@MyDate, ''), getdate());

    INSERT INTO MyTable1 ([name], [MyDate])

    SELECT [name], @inputDate AS Expr1

    FROM MyTable2

    END

    With that said - using NULL as the default value would be much better...

    ALTER PROCEDURE [dbo].[MyStoredProcedure]

    @MyDate date = NULL

    AS

    BEGIN

    INSERT INTO MyTable1 ([name], [MyDate])

    SELECT [name], coalesce(@MyDate, getdate()) AS Expr1

    FROM MyTable2

    END

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 10 posts - 1 through 9 (of 9 total)

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