Converting nvarchar to date
-
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)
-
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!
-
"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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - 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? 🙂
- 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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.
-
DECLARE @DateStr NVARCHAR(50) = N'12172019';
SELECT @DateStr,
CAST(CONCAT(RIGHT(@DateStr, 4), LEFT(@DateStr, 2), SUBSTRING(@DateStr, 3, 2)) AS DATE);The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
it is defined as [Due Date] (nvarchar(10),null)
- 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I have searched, and none of the solutions have worked. otherwise I wouldn't have posed my issue.
- 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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);
-
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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.
-
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]
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply