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

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

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

Answer 3

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 4

I found this helpful for my conversion, without string manipulation. https://learn.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.

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

Answer 6

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

If your target column is datetime you don't 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:

Answer 7

I'd use STUFF to insert dividing chars and then use CONVERT with the appropriate style. Something like this:

DECLARE @dt VARCHAR(100)='111290';
SELECT CONVERT(DATETIME,STUFF(STUFF(@dt,3,0,'/'),6,0,'/'),3)

First you use two times STUFF to get 11/12/90 instead of 111290, than you use the 3 to convert this to datetime (or any other fitting format: use . for german, - for british...) More details on CAST and CONVERT

Best was, to store date and time values properly.

• This should be either "universal unseparated format" yyyyMMdd
• or (especially within XML) it should be ISO8601: yyyy-MM-dd or yyyy-MM-ddThh:mm:ss More details on ISO8601

Any culture specific format will lead into troubles sooner or later...

Answer 8

use Try_Convert:Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

DECLARE @DateString VARCHAR(10) ='20160805'
SELECT TRY_CONVERT(DATETIME,@DateString)

SET @DateString ='Invalid Date'
SELECT TRY_CONVERT(DATETIME,@DateString)

Link:MSDN TRY_CONVERT (Transact-SQL)

Answer 9

I had luck with something similar:

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

Answer 10

This seems the easiest way..

SELECT REPLACE(CONVERT(CHAR(10), GETDATE(), 110),'-','')

Answer 11

SQL standard dates while inserting or updating Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

So if you are inserting/Updating below 1/1/1753 you will get this error.

Answer 12

DECLARE @d char(8)
SET @d = '06082020'    /* MMDDYYYY means June 8. 2020 */
SELECT CAST(FORMAT (CAST (@d AS INT), '##/##/####') as DATETIME)

Result returned is the original date string in @d as a DateTime.

Answer 13

The root cause of this issue can be in the regional settings - DB waiting for YYYY-MM-DD while an app sents, for example, DD-MM-YYYY (Russian locale format) as it was in my case. All I did - change locale format from Russian to English (United States) and voilà.