Converting nvarchar to date

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4389

    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)

  • Jacob Wilkins

    One Orange Chip

    Points: 27880

    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!

  • Phil Parkin

    SSC Guru

    Points: 244133

    "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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jacob Wilkins

    One Orange Chip

    Points: 27880

    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: 244133

    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"! 🙂

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4389

    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: 244133

    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 1 week, 4 days ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4389

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

  • Phil Parkin

    SSC Guru

    Points: 244133

    cbrammer1219 wrote:

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

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4389

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

  • Phil Parkin

    SSC Guru

    Points: 244133

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4389

    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: 244133

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jacob Wilkins

    One Orange Chip

    Points: 27880

    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: 4389

    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 26 total)

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