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 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

Answer 1

Use the Convert function or Cast it...

Convert and Cast

Answer 2

Look at CAST / CONVERT in BOL that should be a start.

If your target column is datetime you dont need to convert it, SQL will do it for you.

Otherwise

CONVERT(datetime, '20090101')

Should do it.

This is a link that should help as well: http://www.karaszi.com/SQLServer/info%5Fdatetime.asp

Answer 3

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)

Answer 4

I think CONVERT is the best choice as you can include a style (date format), so the USA default would be 110 which is mm-dd-yyyy.

Answer 5

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)

Answer 6

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.

Answer 7

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.

Answer 8

I had luck with something similar:

Convert(DATETIME, CONVERT(VARCHAR(2), @Month) + '/' + CONVERT(VARCHAR(2), @Day)
+ '/' + CONVERT(VARCHAR(4), @Year))