Converting nvarchar to date

Question

Converting nvarchar to date

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
March 23, 2020 at 8:42 pm

#3736417

I am not getting why this isn't working? Any suggestion plz.
Conversion failed when converting date and/or time from character string.
CAST(Convert(nvarchar,[due date],104) as DATE)

Viewing 15 posts - 1 through 15 (of 25 total)

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

A few points....

Style 104 is a dmy format, so one potential explanation is that your language is US English or another for which the dateformat default is mdy. That would cause the above error for many dates (today is a good example, because in dmy we get 23.03.2020, but 23 is not a valid month).

There are a lot of other reasons this could happen, but it's tough to say for sure without access to the data. The easiest way to see what rows are causing it to throw the error is to replace the CAST to DATE with a TRY_CONVERT to DATE, and filter to rows where the result of TRY_CONVERT is null

Is there a good reason the due_date column is being converted to nvarchar and then to date?

Cheers!

This reply was modified 4 years, 9 months ago by Jacob Wilkins.
This reply was modified 4 years, 9 months ago by Jacob Wilkins.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

"Not working" is not helpful. What is the error message?

NVARCHAR needs a length.

What is the data type of [due date]? If it's a date, why not just

CAST([due date] as DATE)

?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

Phil Parkin wrote:

"Not working" is not helpful. What is the error message?
NVARCHAR needs a length.
What is the data type of [due date]? If it's a date, why not just
CAST([due date] as DATE)
?

If it's a DATE, why cast it to a DATE at all? 🙂

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Jacob Wilkins wrote:

Phil Parkin wrote:
"Not working" is not helpful. What is the error message?
NVARCHAR needs a length.
What is the data type of [due date]? If it's a date, why not just
CAST([due date] as DATE)
?
If it's a DATE, why cast it to a DATE at all? 🙂

Aargh, I meant "If it's a DATETIME"! 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 5

Thanks for getting back to me, the Due Date is formatted like this 12172019 and does have zeros in the field also, it is being imported from an excel spreadsheet. I'm trying to convert it as a date because it is defined as a nvarchar when it was originally imported some years ago.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

DECLARE @DateStr NVARCHAR(50) = N'12172019';
SELECT @DateStr,
    CAST(CONCAT(RIGHT(@DateStr, 4), LEFT(@DateStr, 2), SUBSTRING(@DateStr, 3, 2)) AS DATE);

This reply was modified 4 years, 9 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 7

cbrammer1219

SSCarpal Tunnel

Points: 4476

March 23, 2020 at 9:25 pm

#3736441

it is defined as [Due Date] (nvarchar(10),null)

Phil Parkin SSC Guru Points: 246985 More actions · Answer 8

cbrammer1219 wrote:

it is defined as [Due Date] (nvarchar(10),null)

OK, so my code should work, with some refinements to handle the zeros.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 9

I have searched, and none of the solutions have worked. otherwise I wouldn't have posed my issue.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 10

cbrammer1219 wrote:

I have searched, and none of the solutions have worked. otherwise I wouldn't have posed my issue.

Are you saying that my code 'does not work'? If so, please provide more detail, because it appears to work for me.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 11

Are you talking about this code? This doesn't work I get the same error.

DECLARE @DateStr NVARCHAR(50) = N'12172019';

SELECT @DateStr,

CAST(CONCAT(RIGHT(@DateStr, 4), LEFT(@DateStr, 2), SUBSTRING(@DateStr, 3, 2)) AS DATE);

Phil Parkin SSC Guru Points: 246985 More actions · Answer 12

2020-03-24_11-17-29

This is on 2017.

Can someone else please test on 2019? Or maybe explain why it would not work there?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 13

Phil's snippet is not language/dateformat dependent, so should just work.

I'm guessing what is "not working" is the application of that pattern to the OP's original problem.

The OP did say some of the values are just 0, which would fail to convert.

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 14

I got this to work, however if the month is 1-9 it sets the date to '01/01/1900' because of the month not having 2 digits.

CASE WHEN ISDATE(left([Due Date],2) +'/'+ SUBSTRING(right([Due Date],6),1,2)+'/'+ right([Due Date],4)) = 1 then

cast(left([Due Date],2) +'/'+ SUBSTRING(right([Due Date],6),1,2)+'/'+ right([Due Date],4) as date) else '01/01/1900'

end as [Due Date]