-2

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.

tintyethan
  • 1,772
  • 3
  • 20
  • 44
  • 1
    [What have you tried](http://www.whathaveyoutried.com) and what have you searched? – Kermit Feb 01 '13 at 19:48
  • possible duplicate of [convert varchar into datetime in sql server](http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server) and [SQL Server convert string to datetime](http://stackoverflow.com/questions/1135746/sql-server-convert-string-to-datetime) and [SQL Server Convert Varchar to Datetime](http://stackoverflow.com/questions/10247050/sql-server-convert-varchar-to-datetime) and many others – Kermit Feb 01 '13 at 19:48
  • Have you tried `cast(column as datetime)`? – Daniel Kelley Feb 01 '13 at 19:50
  • 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. – tintyethan Feb 01 '13 at 21:26

3 Answers3

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)
Lamak
  • 69,480
  • 12
  • 108
  • 116
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.

Louis Ricci
  • 20,804
  • 5
  • 48
  • 62
  • This is a lot of work when you can just use `CONVERT` – Kermit Feb 01 '13 at 19:52
  • @njk - There isn't a CONVERT style for the mmddyyyy format, unless I'm missing something http://msdn.microsoft.com/en-us/library/ms187928.aspx – Louis Ricci Feb 01 '13 at 20:02
  • @LastCoder And wich one is it? – Lamak Feb 01 '13 at 20:04
  • @Lamak - You're going to have to elaborate a bit. – Louis Ricci Feb 01 '13 at 20:07
  • Sorry, I read your comment as *There **is** a `CONVERT` style* – Lamak Feb 01 '13 at 20:09
  • 1
    The `YYYY-MM-DD` format [isn't safe](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) 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)`. – Andriy M Feb 02 '13 at 17:48
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;
Valentino Vranken
  • 5,597
  • 1
  • 26
  • 28