Converting nvarchar to date

Question

Converting nvarchar to date

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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.

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: 246961 More actions · Answer 2

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:

DDL and consumable data, in the form of INSERT statements, along with expected results
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.
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.

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

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

>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: 1004356 More actions · Answer 4

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.

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: 9303 More actions · Answer 5

>> 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: 246961 More actions · Answer 6

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.

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

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

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: 246961 More actions · Answer 8

Thanks, Jacob, for the correction.

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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.

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: 1004356 More actions · Answer 10

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.

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: 1004356 More actions · Answer 11

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.

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)