How to convert string to datetime in SQL server

Question

1.00/5 (1 vote)

See more:

I have date column which is varchar(50) and it contains mixed dates like dd/mm/yyyy and mm/dd/yyyy now i want to convert the date column values to smalldatetime
but it is giving error

Conversion failed when converting date and/or time from character string. for mm/dd/yyyy 
please give some suggestion so that i can convert this mixed data in smalldatetime  format
Thank you in advance.

What I have tried:

SELECT Convert(date,'01/14/2017',103)

declare  @tbl table
(
date varchar(20)
)


insert into @tbl
select '14/03/2017'

insert into @tbl
select '01/14/2017'


select convert(smalldatetime ,convert(varchar(10),date,103), 103) from @tbl

select convert(smalldatetime,convert(varchar(10),date,112),103) from @tbl

Posted 16-May-17 1:47am

Member 12966735

Updated 6-Jun-20 18:53pm

Add a Solution

Comments

CHill60 16-May-17 7:51am

You can't. Is "03/11/2016" - 11th March or 3rd November?

Paw Jershauge 16-May-17 7:53am

How would you ever know what style to convert from ???

4 solutions

Solution 1

Its is not something i would recomment, but this should work for the two posible combinations you specified

SQL

declare  @tbl table
(
	[date] varchar(20)
)
 

insert into @tbl
select '14/03/2017'
UNION ALL
select '01/14/2017'
 

SELECT CASE WHEN TRY_PARSE([date] AS smalldatetime) IS NULL THEN TRY_PARSE((SUBSTRING([date],4,2) + '/' + SUBSTRING([date],1,2) + '/' +SUBSTRING([date],7,4)) AS smalldatetime) ELSE TRY_PARSE([date] AS smalldatetime) END [convertedtype] FROM @tbl

Result:
convertedtype
2017-03-14 00:00:00
2017-01-14 00:00:00

Posted 16-May-17 2:02am

Paw Jershauge

Comments

CHill60 16-May-17 8:05am

As per the comments in my solution it will potentially misinterpret dates - try adding

UNION ALL select '03/11/2017'

to the table populate - it comes out as 11th March, but the date is 3rd November here.

Paw Jershauge 16-May-17 8:38am

@CHill60, i believe i said that i would not recommend it; but if the first cast fails i would try something else, i'm NOT saying that this is correct...

CHill60 17-May-17 17:26pm

If you use the Reply button next to a comment then the poster is notified that you are responding. I found this response entirely by chance.
The point I am making is that there is NO programmatic way to determine which is the correct date - the OP can capture cast failures and deal with them, but the example I gave will not fail a CAST ... but could still be very very wrong

Solution 3

hi my solution is

SELECT Convert(datetime,'2017/05/05',103)

2017-05-05 00:00:00.000

SELECT Convert(date,cast('2017/05/05' as date),103)

2017-05-05

SELECT Convert(datetime,'05/05/2017',103)

2017-05-05 00:00:00.000

SELECT Convert(date,cast('05/05/2017' as date),103)

2017-05-05

SELECT Convert(smalldatetime,'2017/05/05',103)

2017-05-05 00:00:00.000


SELECT Convert(smalldatetime,cast('2017/05/05' as date),103)

2017-05-05 00:00:00.000

Posted 16-May-17 20:20pm

vinuvasahanponniah

Updated 16-May-17 20:23pm

v2

Comments

CHill60 17-May-17 17:23pm

Works fine for that date doesn't it. Now try '03/11/2017' ... is that 11th March or 3rd November? Are you sure?

vinuvasahanponniah 17-May-17 23:45pm

no.wait

Solution 4

No... this is all wrong. If you have a column containing a mix of mm/dd/yyyy and dd/mm/yyyy, there is absolutely no way that you will be able to discern if something like 7/6/2000 is the 7th of June or the 6th of July just from the information in that column. You MUST talk with whomever provided you with the data a get them to settle on a format for dates. It would be best if you used the ISO format of YYYYMMDD. DON'T settle for YYYY-MM-DD because if it's for the French language, it comes up as YYYY-DD-MM.

Posted 6-Jun-20 18:54pm

Jeff Moden

Comments

CHill60 8-Jun-20 9:56am

I pointed out the problem with certain dates 3 years ago.
As to the "date format", I disagree entirely. ISO 8601 allows for the '-' separator, in fact if only year and month are being used to indicate a date then the '-' must be used. I've handled dates for many cultural environments and have never come across YYY-MM-DD being misinterpreted for any language. Happy to stand corrected if you can provide the evidence.
However, dates should be stored as Dates, not strings, then there is no concept of "format".

Jeff Moden 8-Jun-20 14:47pm

Yes... ISO 8601 allows for dashes as delimiters. And, no... the dashes do not need to be included in the YYYYMM format although I certainly think they make things much more readable and DO, in fact, identify that it's formatted correctly.

But... ISO 8601 says that dashes are an alternate format from the YYYYMMDD format, which is the primary format. "alternate" formats are frequently bastardized and, in SQL Server, if the language is French, the format using dashes is YYYY-DD-MM.

The real key here is that my suggestion of using YYYYMMDD is actually secondary and the OP must have folks settle on a standard that is completely unambiguous. Unfortunately, in SQL Server, YYYY-xx-xx is NOT completely unambiguous.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-05-16T02:02:00

Assuming this table is under your control and you are trying to do some homework or similar ... NEVER store dates in text-type columns (char, nchar, varchar, nvarchar or text). ALWAYS store dates in columns of types DATE or DATETIME. Leave any formatting of the date data to the UI layer rather than when retrieving from the database.

If you are stuck with this data then you have problems determining which format to use ... it will be fine for any dates after the 12th of any month because the format DD/MM/YYYY or MM/DD/YYYY will be obvious (there is no 13th month so wherever that >12 value appears must be the DAY of the date. You might be able to process the bulk of that data using a WHILE loop, leaving the ambiguous data to be handled manually

How to convert string to datetime in SQL server

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us