Convertion from Varchar to Datetime

Question

Convertion from Varchar to Datetime

karunakar2351

Right there with Babe

Points: 729
More actions
August 5, 2013 at 4:45 am

#276718

How do I convert a string of format -> mmddyyyy into datetime in SQL Server 2008?
My target column in 'DateTime'
I have tried with Convert and most of the Date style values - I get a 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.' error message.

Viewing 5 posts - 1 through 4 (of 4 total)

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

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 1

Looking at BOL there isn't a style no. for your date format. You'll have to do some string manipulation with LEFT() and RIGHT() to sort it out.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Can you please post what you tried?

Also, post the sample data and what would be your desired output from that

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SELECT CONVERT(DATETIME,RIGHT(MyDate,4)+LEFT(MyDate,4),112)

FROM (SELECT MyDate = '12252013') d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

If I'm reading your OP correctly, you're trying to emulate the Oracle TO_CHAR function, which SQL Server doesn't have. The real stumbling block you run into here is that the individual parts of the date are integers, so you can't simply concatenate them together, as SQL will add them instead of concatenate them.

The direct approach is to convert them to strings and then concatenate them, but there's a problem with this.

DECLARE @dtmNow datetime;

SET @dtmNow = GetDate();

SELECT CONVERT(Varchar, DATEPART(month, @dtmNow)) + CONVERT(Varchar, DATEPART(day, @dtmNow)) + CONVERT(Varchar, DATEPART(year, @dtmNow))

The problem with this is that when a month or day is a single digit, you're going to get a result that is not 8 characters long, which will likely cause you problems later. So, the real solution is to use this concatenation approach but to also ensure that each part is the length you need.

DECLARE @dtmNow datetime;

SET @dtmNow = GetDate();

SELECT RIGHT('00' + CONVERT(Varchar, DATEPART(month, @dtmNow)), 2) + RIGHT('00' + CONVERT(Varchar, DATEPART(day, @dtmNow)), 2) + CONVERT(Varchar, DATEPART(year, @dtmNow))

It's worth pointing out here that if your goal is to store them as an integer, please consider yyyymmdd instead. The field is, but its very definition, sortable without any extra work. If your goal is presentation or other manipulation, disregard this note. In either case, definitely store date values as date data types - it will probably save you a significant amount of pain later in life.

HTH

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions