convert string to datetime in sql server [duplicate]

Question

I have a column of dates with no delimiters. The column is nvarchar. The strings are consistent in length and format of MMDDYYYY. How can I convert these values to datetime?

edit - this question is in reference to sql server.

possible duplicate of convert varchar into datetime in sql server and SQL Server convert string to datetime and SQL Server Convert Varchar to Datetime and many others — Kermit
– Kermit, Commented Feb 1, 2013 at 19:48
I checked the suggestions first to avoid adding a duplicate comment. I have tried simply changing the column to datetime in desiger, which erases the data, and I've tried importing as datetime, which results in the data not being imported for that column. — Ethan Pelton
– Ethan Pelton, Commented Feb 1, 2013 at 21:26

Lamak · Accepted Answer · 2013-02-01 19:51:35Z

3

Assuming SQL Server:

DECLARE @A NVARCHAR(10)
SET @A = '11302012'

SELECT CONVERT(DATETIME,LEFT(@A,2) + '/' + 
       SUBSTRING(@A,3,2) + '/' + RIGHT(@A,4),101)

answered Feb 1, 2013 at 19:51

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Louis Ricci · Accepted Answer · 2013-02-04 12:31:48Z

0

BEGIN
DECLARE @d DATETIME
DECLARE @s NVARCHAR(32)
SET @s = N'12012013'
SET @d = SUBSTRING(@s, 5,4) + SUBSTRING(@s, 1,2) + SUBSTRING(@s, 3,2)
SELECT @d
END

You just have to mangle the string into a format SQL server can parse correctly into a date. In the above it's the YYYYMMDD format.

EDIT Removed "-"'s because French language settings break them.

edited Feb 4, 2013 at 12:31

answered Feb 1, 2013 at 19:50

Louis Ricci

21.1k5 gold badges50 silver badges64 bronze badges

6 Comments

Kermit Over a year ago

This is a lot of work when you can just use CONVERT

Louis Ricci Over a year ago

@njk - There isn't a CONVERT style for the mmddyyyy format, unless I'm missing something msdn.microsoft.com/en-us/library/ms187928.aspx

Lamak Over a year ago

@LastCoder And wich one is it?

Louis Ricci Over a year ago

@Lamak - You're going to have to elaborate a bit.

Andriy M Over a year ago

The YYYY-MM-DD format isn't safe for implicit conversion to datetime. On the other hand, YYYYMMDD is, and so you could simplify your code down to just two SUBSTRING calls: SUBSTRING(@s, 5, 4) + SUBSTRING(@s, 1, 4).

|

Valentino Vranken · Accepted Answer · 2013-02-04 13:39:41Z

0

First change the format to the one that always works no matter what server settings (YYYYMMDD) using two simple string functions, then convert to datetime:

declare @datestring varchar(8) = '11302012';

select CONVERT(datetime, RIGHT(@datestring, 4) + LEFT(@datestring, 4)) ConvertedDatetime;

answered Feb 4, 2013 at 13:39

Valentino Vranken

5,8551 gold badge28 silver badges28 bronze badges

Collectives™ on Stack Overflow

convert string to datetime in sql server [duplicate]

3 Answers 3

Comments

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Linked

Related