December 23, 2016 at 11:36 am
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
*/
December 23, 2016 at 11:44 am
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
December 23, 2016 at 11:49 am
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;
December 23, 2016 at 12:06 pm
Thanks for the response. Tried to add the style and it is still giving me the same answer.
December 23, 2016 at 12:08 pm
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?
December 23, 2016 at 12:50 pm
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
December 23, 2016 at 12:54 pm
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.
December 23, 2016 at 12:55 pm
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.
December 24, 2016 at 8:59 am
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.
December 24, 2016 at 10:23 am
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