Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Convert varchar to Date as MM/DD/YYYY in SQL

I have a table contain user input [EXP Date] as varchar:
1/11/2011
3/9/2011
3/9/11
I am trying to convert them to date:
Convert(datetime, [EXP Date],101), but I got the error message:
Conversion failed when converting datetime from character string.

This issue have bothered me a long time and i tired walk around it, but this time I have to figure out something before I can move on to do some dates caculation.

Does any one have idea of this?

Thank  you very much.

Fighting Miao
0
FinghtingMiao
Asked:
FinghtingMiao
  • 7
  • 4
  • 3
  • +1
5 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
Got to 'SET DATEFORMAT' on this page: http://msdn.microsoft.com/en-us/library/ms186724.aspx  Default date format in all the SQL databases that I have seen is YYYY/MM/DD .  You have to do something to get it to accept something different.  Looks like 'SET DATEFORMAT' might do what you want.
0
 
Ron MalmsteadInformation Services ManagerCommented:
Is it possible you have " " spaces or a Cr or Lf  character present in the field?

Your syntax is fine and should work.
0
 
Ron MalmsteadInformation Services ManagerCommented:
SELECT CONVERT(Datetime,'1/11/2011',101)

Works....


SELECT CONVERT(Datetime,'1/11/2011
',101)

Doesn't work...
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
FinghtingMiaoAuthor Commented:
xuserx2000:
Yes, I awared that ""spaces ...etc will cause the problem, so I filter them out before I do the converstion.
Thank you.
0
 
JestersGrindCommented:
The issue is that you have a mix of 2 digit and 4 digit years.  Try this.

SELECT CONVERT(datetime, Convert(datetime, [EXP Date]), 101)

Greg

0
 
Ron MalmsteadInformation Services ManagerCommented:
Try this to test...

SELECT CONVERT(Datetime,Replace(Replace([EXP Date],CHAR(13),''),CHAR(10),''),101)
0
 
Ron MalmsteadInformation Services ManagerCommented:
Spaces at beginning or end of the string actually shouldn't affect it, but carriage return or newline char definitely would.
0
 
Ron MalmsteadInformation Services ManagerCommented:
JestersGrind may have it.


This works for me...

SELECT CONVERT(Datetime,cast('3/9/11' as datetime),101)


This doesn't...

SELECT CONVERT(Datetime,'3/9/11',101)
0
 
FinghtingMiaoAuthor Commented:
JestersGrind:
yes, when i do
Convert (datetime, '1/1/2011', 101) works
but if i do
convert (datetime,'3/9/11',101) then does not work.
Let me try your solutiuon to see if it work.
 thanks.
0
 
Ron MalmsteadInformation Services ManagerCommented:
SELECT CONVERT(Datetime,cast([EXP Date] as datetime),101)

0
 
JestersGrindCommented:
I can give you a more complete explanation.  The 101 in your query is the style.  Anything over 100 is specifically for 4 digit dates.  Anything under 100 is for 2 digit dates.  You could just remove the style component and it should work for both.  If there is a particular format that you would like it in, let us know and we can help.

Greg

0
 
JestersGrindCommented:
Actually you could update your table so that all of the dates are in a 4 digit year format.  Something like this.

UPDATE YourTable SET [EXP Date] = CONVERT(VARCHAR(50), Convert(datetime, [EXP Date]), 101)

Greg

0
 
FinghtingMiaoAuthor Commented:
JestersGrind:
and
xuserx2000:

The solution works!  The reason is mix of 2 and 4 digits of year.
Thank you guys very much for that quick response!
0
 
FinghtingMiaoAuthor Commented:
You guys are very efficient! thanks. I learned a lot from you all.
0
 
Ron MalmsteadInformation Services ManagerCommented:
Please give the point to Jester, it was his solution.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now