nvarchar date won't convert to date time

Question

I can't seem to find an applicable solution to my issue. I am trying to pass through an NVARCHAR date and then convert it. When trying to trouble shoot my issue that I am getting:

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

Nothing seems to be helping so far. I tried several suggestions around google and on SO but I keep getting the above message. I tried this suggestion from SO but that also didn't seem to help.

   DECLARE @ConvertCancelDate DATETIME
    DECLARE @incCancelDate nvarchar
    SET @incCancelDate = '2018-07-04'

    -- need to convert the cancel date from nvarchar to datetime
    SET @ConvertCancelDate =  CONVERT(DATETIME, @incCancelDate)

I have to at least ask...why are you putting this in a nvarchar anyway? Certainly you don't need nvarchar, but since you are just converting it to a datetime without using try_parse or anything why not just put it directly into a datetime and let the implicit conversion happen? Or better yet, get the source to store it as a datetime and eliminate the insanity of storing dates as strings. — Sean Lange, Aug 13 '18 at 21:10
@SeanLange unfortunately, the source is another company (I am pulling from a 3rd party API for reporting purposes). — ggiaquin16, Aug 13 '18 at 23:47

score 2 · Accepted Answer · answered Aug 13 '18 at 20:39

2

The problem is you aren't giving the @incCancelDate a size, so it assumes its a single character string.

Change this:

DECLARE @incCancelDate nvarchar

To:

DECLARE @incCancelDate nvarchar(10)

answered Aug 13 '18 at 20:39

sgeddes

62,311
6
61
83

wow... now I feel dumb for missing that..... does that also work for incoming parameters? that declaration was made just to trouble shoot. – ggiaquin16 Aug 13 '18 at 20:41
2

@ggiaquin16 : Always explicitly specify the length of any `char`/`varchar`/`nvarchar` regardless of whether it's a local variable or a parameter to a stored procedure or whatever else. – Joe Farrell Aug 13 '18 at 20:42

score 1 · Answer 2 · answered Aug 13 '18 at 20:41

You're having a problem because you didn't define the length for your @incCancelDate variable, which means it is nvarchar(1).

To see this, try this:

DECLARE @incCancelDate NVARCHAR;
SET @incCancelDate = N'2018-07-04';
SELECT @incCancelDate;

To fix it, do this:

DECLARE @ConvertCancelDate DATETIME;
DECLARE @incCancelDate NVARCHAR(10);
SET @incCancelDate = N'2018-07-04';

-- need to convert the cancel date from nvarchar to datetime
SET @ConvertCancelDate = CONVERT(DATETIME, @incCancelDate, 111);

nvarchar date won't convert to date time

2 Answers2