Converting nvarchar to date

  • Jeff Moden

    SSC Guru

    Points: 996046

    cbrammer1219 wrote:

    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]

     

    Would you PLEASE post a set of rows that actually depict the nuances of the data that you actually have so people can actually help you with a solution?  You know the "rules"... same as always.  Please see the first link in my signature line below if you've forgotten. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Phil Parkin

    SSC Guru

    Points: 244134

    cbrammer1219 wrote:

    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);

    For someone with so many points, I would have expected a lot more from your posts:

    1. DDL and consumable data, in the form of INSERT statements, along with expected results
    2. A total avoidance of phrases like "it's not working." Instead, descriptions of what actions were taken and which errors were made should be provided.
    3. An absence of posts telling people that their correct code (based on the example(s) you have provided) results in errors, without any demonstration of that fact.

    Your working solution would not pass a code review in my company. It ignores ISO date formats and unnecessarily repeats the long piece of string-manipulation code.

    Had you taken the time to follow only point (1) above, your problem would have been solved immediately, and to a level of quality somewhat beyond that in your own version.

    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.

  • jcelko212 32090

    SSCrazy Eights

    Points: 8948

    >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.<<Clean up the souce data instead of kludging repairs on the fly

    Take some time to learn about the ISO standards for date and time. SQL specifically went out of its way to add temporal datatypes. In particular, the only display format allowed in the ANSI/ISO standards for SQL is "yyyy-mm-dd", considering that this is the second most popular ISO standard. After the metric system, you might want to start using it 🙂 You can also include time zones, too.

     

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jeff Moden

    SSC Guru

    Points: 996046

    jcelko212 32090 wrote:

    >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.<<Clean up the souce data instead of kludging repairs on the fly

    Take some time to learn about the ISO standards for date and time. SQL specifically went out of its way to add temporal datatypes. In particular, the only display format allowed in the ANSI/ISO standards for SQL is "yyyy-mm-dd", considering that this is the second most popular ISO standard. After the metric system, you might want to start using it 🙂 You can also include time zones, too.

    Stop it, Joe.  You know that YYYY-MM-DD is the alternate format and it doesn't work in certain languages in SQL Server.  You also know that the primary ISO format is YYYYMMDD.  Supposedly, you helped write the standard so why do you continue to publish bad information?  That's actually not a question but an observation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090

    SSCrazy Eights

    Points: 8948

    >> You know that YYYY-MM-DD is the alternate format and it doesn't work in certain languages in SQL Server. You also know that the primary ISO format is YYYYMMDD. Supposedly, you helped write the standard so why do you continue to publish bad information? <<

    Please quit misquoting me. What I keep saying is that the only format allowed in th ANSI/ISO standards for the SQL language is the YYYY-MM-DD . This is the only format in ANSI/ISO standard for SQL. That's the only standard I voted on or worked with. I do not question that there are other ISO – 8601 date formats. In particular, I really like the week within year format (really handy for weekly computations) and the ordinal date format (handy for simple date counts within the year).

    Yes, there are national conventions (they are usually not international standards) which can get pretty weird. My favorite example is the three-letter month abbreviations in Czech and Slovak. In spite of having been in the same country for a long time, these language groups use either the Christian month names or the traditional Slavic month names. They are nothing alike.

    The reasons we decided to stick with one and only one ISO standard are:

    1) it is language independent

    2) the character string sorts correctly

    3) it can be parsed separately from either an integer or another kind of character string. The dashes make the regular expiration fairly simple to parse. This is why we rejected YYYYMMDD string format.

    Jeff, I would assume by now you have noticed that even Microsoft is defaulting to this standard in their new temporal data types.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Phil Parkin

    SSC Guru

    Points: 244134

    The fact that the result returned by

    SELECT CAST('YYYY-MM-DD' AS date);

    is language dependent, and therefore non-deterministic, is enough reason for me to use the 'YYYYMMDD' format, for which the above expression is deterministic.

    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

    That particular result actually isn't, as far as I understand it, because ydm is not supported for the date datatype

    For example:

    SET LANGUAGE Deutsch;
    SELECT cast_as_datetime=CAST('2020-12-25' AS DATETIME);
    SELECT cast_as_date =CAST('2020-12-25' AS DATE);
    GO

    SET LANGUAGE English;
    SET DATEFORMAT ydm;
    SELECT cast_as_datetime=CAST('2020-12-25' AS DATETIME);
    SELECT cast_as_date =CAST('2020-12-25' AS DATE);
    GO

    The general point is still a good one, of course. Using YYYYMMDD is just more consistent in the SQL Server implementation of all these datatypes.

    Cheers!

  • Phil Parkin

    SSC Guru

    Points: 244134

    Thanks, Jacob, for the correction.

    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.

  • Jeff Moden

    SSC Guru

    Points: 996046

    jcelko212 32090 wrote:

    >> You know that YYYY-MM-DD is the alternate format and it doesn't work in certain languages in SQL Server. You also know that the primary ISO format is YYYYMMDD. Supposedly, you helped write the standard so why do you continue to publish bad information? <<

    Please quit misquoting me. What I keep saying is that the only format allowed in th ANSI/ISO standards for the SQL language is the YYYY-MM-DD . This is the only format in ANSI/ISO standard for SQL. That's the only standard I voted on or worked with. I do not question that there are other ISO – 8601 date formats. In particular, I really like the week within year format (really handy for weekly computations) and the ordinal date format (handy for simple date counts within the year).

    Yes, there are national conventions (they are usually not international standards) which can get pretty weird. My favorite example is the three-letter month abbreviations in Czech and Slovak. In spite of having been in the same country for a long time, these language groups use either the Christian month names or the traditional Slavic month names. They are nothing alike.

    The reasons we decided to stick with one and only one ISO standard are:

    1) it is language independent

    2) the character string sorts correctly

    3) it can be parsed separately from either an integer or another kind of character string. The dashes make the regular expiration fairly simple to parse. This is why we rejected YYYYMMDD string format.

    Jeff, I would assume by now you have noticed that even Microsoft is defaulting to this standard in their new temporal data types.

    Ah... OK.  Understood.  But you're still incorrect when it comes to Microsoft.  The YYYY-MM-DD format is labeled in Books Online as "ISO8601" (format #23, 126 and 127).  The YYYYMMDD format is labeled as "ISO" (format # 112).

    And, there's a real problem with the YYYY-MM-DD format as the following code clearly demonstrates.

        SET LANGUAGE ENGLISH;
    SELECT CONVERT(DATETIME,'2020-01-02');

    SET LANGUAGE FRENCH;
    SELECT CONVERT(DATETIME,'2020-01-02');

    Here's are the results from that...

    English
    -----------------------
    2020-01-02 00:00:00.000

    French
    -----------------------
    2020-02-01 00:00:00.000

    Yeah... fine "standard" MS followed there.  I will agree that at least the DATE datatype does NOT have that same problem.

    Ok, ok... I know what's coming next because of what you said in your previous post... (emphasis is mine)...

    "In particular, the only display format allowed in the ANSI/ISO standards for SQL is "yyyy-mm-dd""

    So, what is the document number for that standard?  I'll go find it and then I can stop chiding you for misquoting a standard and apologize for me riding your donkey about it.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996046

    Instead of waiting for you, Joe, I found ISO/IEC 9075-1, ISO/IEC 9075-2, ISO/IEC TR 19075-2, (all 3 are part of ISO SQL standard) and revisited the ISO 8601 spec.

    ISO/IEC 9075-1 make no mention of date formats and does not contain even "yyyy-mm".  ISO/IEC 9075-2 doesn't either but it DOES say that ISO 8601 is one of the related references in the front matter.  It does not cite any specific paragraphs, though.

    ISO/IEC TR 19075-2 appear to be an abbreviated (Technical Report is what they call it) regurgitation of what is in the ISO/IEC 9075-2 spec.

    The ONLY standard that seems to have any information about formatting dates and times (not to be mistaken with periods or intervals) is ISO 8601.

    So in order for me to apologize properly, I need to know the spec number that you're citing as me misquoting you.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996046

    Joe... I'm still waiting for you to identify the ISO Standard by number for what you're talking about that clearly identifies that the YYYY-MM-DD format is the only supported format in ISO SQL. Since you're so familiar with it, you should know it off the top of your head.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 16 through 26 (of 26 total)

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