Convert varchar into datetime in SQL Server

Question

How do I convert a string of format mmddyyyy into datetime in SQL Server 2008?

My target column is in DateTime

I have tried with Convert and most of the Date style values however I get an error message:

'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'

Solution 1

OP wants mmddyy and a plain convert will not work for that:

select convert(datetime,'12312009')

Msg 242, Level 16, State 3, Line 1 
The conversion of a char data type to a datetime data type resulted in 
an out-of-range datetime value

so try this:

DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

OUTPUT:

-----------------------
2009-12-31 00:00:00.000

(1 row(s) affected)

Solution 2

SQL Server can implicitly cast strings in the form of 'YYYYMMDD' to a datetime - all other strings must be explicitly cast. here are two quick code blocks which will do the conversion from the form you are talking about:

version 1 uses unit variables:

BEGIN 
DECLARE @input VARCHAR(8), @mon CHAR(2), 
@day char(2), @year char(4), @output DATETIME

SET @input = '10022009'   --today's date


SELECT @mon = LEFT(@input, 2), @day = SUBSTRING(@input, 3,2), @year = RIGHT(@input,4)

SELECT @output = @year+@mon+@day 
SELECT @output 
END

version 2 does not use unit variables:

BEGIN 
DECLARE @input CHAR(8), @output DATETIME
SET @input = '10022009' --today's date 

SELECT @output = RIGHT(@input,4) + SUBSTRING(@input, 3,2) + LEFT(@input, 2)

SELECT @output
END

Both cases rely on sql server's ability to do that implicit conversion.

Solution 3

I found this helpful for my conversion, without string manipulation. https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

CONVERT(VARCHAR(23), @lastUploadEndDate, 121)

yyyy-mm-dd hh:mi:ss.mmm(24h) was the format I needed.

Solution 4

Likely you have bad data that cannot convert. Dates should never be stored in varchar becasue it will allow dates such as ASAP or 02/30/2009. Use the isdate() function on your data to find the records which can't convert.

OK I tested with known good data and still got the message. You need to convert to a different format becasue it does not know if 12302009 is mmddyyyy or ddmmyyyy. The format of yyyymmdd is not ambiguous and SQL Server will convert it correctly

I got this to work:

cast( right(@date,4) + left(@date,4) as datetime)

You will still get an error message though if you have any that are in a non-standard format like '112009' or some text value or a true out of range date.

Solution 5

Convert would be the normal answer, but the format is not a recognised format for the converter, mm/dd/yyyy could be converted using convert(datetime,yourdatestring,101) but you do not have that format so it fails.

The problem is the format being non-standard, you will have to manipulate it to a standard the convert can understand from those available.

Hacked together, if you can guarentee the format

declare @date char(8)
set @date = '12312009'
select convert(datetime, substring(@date,5,4) + substring(@date,1,2) + substring(@date,3,2),112)

View more solutions

Comments

[dwerner]

+1, but wouldn't it be nice to not have to splice strings? techonthenet.com/oracle/functions/to_date.php

[dburges]

@dwerner, if you don't want to have to do stuff like that then don't store in an incorrect datatype.

[dwerner]

Correct me if I'm wrong, but that was the problem proposed by the question. My suggestion is to use a function tailored to the purpose.

[Lucas Leblanc]

Your "suggestion" seems like run-of-the-mill corporate shilling to me. Is a suggestion to use a totally different database technology useful or practical to virtually every database developer, even the one who are lead architects? Not really. Seems like it might be easier to simply store the information not as a varchar string, or at least as an ISO-formatted one.

[KM.]

@LucasLeblanc, you must consider what the OP is asking for. The OP does not elaborate on WHY the need this, possibly they are interfacing with things they can not change. Also, I would never advise you store dates as strings, that is how you end up with problems like this.

[Lucas Leblanc]

That's what I said, in regards to data type, although reading it again my comment wasn't worded that well. I should have said "seems like it might be easier to store the information as anything other than a varchar string."

[Photops]

this is very useful for when you have string formats like 201901 or 201905

[Jeff Moden]

But that's not the format the OP has. The OP is stuck with converting mmddyyyy to a datetime.

[Jeff Moden]

But that's not the format the OP has. The OP is stuck with converting mmddyyyy to a datetime.

[Jeff Moden]

But that's not the format the OP has. The OP is stuck with converting mmddyyyy to a datetime.

[Jeff Moden]

The OP had no variables to work from. Just a string in the MMDDYYYY format.

[Jeff Moden]

OP is stuck with MMDDYYYY format. Your info doesn't actually help the op.

[Jeff Moden]

Doesn't work for the OP. The OP is stuck with a format of MMDDYYYY.

[Jeff Moden]

But that's not the format the OP has. The OP is stuck with converting mmddyyyy to a datetime.

[kiwichen]

Jeff Moden agreed, answer updated to reflect the requirement of the question.

[Jeff Moden]

Thanks for the feedback. Just as an FYI if you don't already know it, the FORMAT function usually take 43 times (or worse) more time to execute than even some of the more complex CAST/CONVERT functionality that people come up with. I generally avoid it like the plague because it's so slow.

[Jeff Moden]

After doing some testing on a million random date strings in the mmddyyyy format like the OP has, this turns out to be both the fastest to run and easiest to code. There are other methods that will tie it for performance but, out of all the code that actually works as the OP asked on this thread, this one wins every time. It's not by more than 25ms on a million rows but, considering the ease of coding and the load of other methods I tested, this one wins.