# 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."

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

• Phil Parkin

SSC Guru

Points: 244134

cbrammer1219 wrote:

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.

• 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."

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.

• 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);GOSET 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.000French-----------------------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."

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."

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."