CAST nvarchar to DATETIME

Question

I've recently imported some spatial data into SQL 2008 from SDF. During the import process, DateTime fields were imported as nvarchar(254). An example of how the data was imported is this: '20130515103000'

In setting up my view, I used SELECT CAST(survey_date AS DATETIME) AS Expr1 and have the following Error:

Conversion failed when converting date and/or time from character string.

From what I can tell, it looks like I may need to reformat my data to conform to the ISO-8601 format before casting or converting the data. I'm not sure how to go about doing this.

Solution 1

It looks like you need some string manipulation as your date string isn't in a recognized format.

There might be a simpler way, but this works in SQL Server:

DECLARE @string VARCHAR(255) = '20130515103000'
SELECT CAST(LEFT(@string,8)+' '+SUBSTRING(@string,9,2)+':'+SUBSTRING(@string,11,2)+'.'+RIGHT(@string,2) AS DATETIME)

Note, I'm assuming the format of your string is "yyyyMMDDHHMMSS" and using 24 hours since AM/PM is not indicated.

Update: The variable is just for testing, to implement it just replace the variable with your datetime string field:

SELECT CAST(LEFT(survey_date,8)+' '+SUBSTRING(survey_date,9,2)+':'+SUBSTRING(survey_date,11,2)+'.'+RIGHT(survey_date,2) AS DATETIME) AS Expr1

Solution 2

This is using Stuff() function.

First change yyyymmddHHMMSS to yyyymmdd HH:MM:SS and then convert it to a Datetime.

--Example:
Declare @mydate nvarchar(250) = '20130515103000'
Select convert(datetime, stuff(stuff(stuff(@mydate, 9, 0,' '), 12,0,':'), 15,0,':'))

--Applied to your table column
Select convert(datetime, stuff(stuff(stuff(survey_date, 9, 0,' '), 12,0,':'), 15,0,':')) AS Expr1
From yourTable

Fiddle demo

Solution 3

You need to convert the input string into the normal datetime format before converting. Try:

Declare  @InputDateFormat  varchar(max)
Declare  @NormalFormatDateTime varchar(max)
Declare  @ConvertedDateTime datetime

--Declare
set  @InputDateFormat  = '20131020215735' --- YYYYMMDDHHMMSS

--Convert to the normal SQL format for a DateTime
set @NormalFormatDateTime =(select SUBSTRING(@InputDateFormat,0,5) + '/' + SUBSTRING(@InputDateFormat,5,2)  + '/' + SUBSTRING(@InputDateFormat,7,2) +  ' ' + SUBSTRING(@InputDateFormat,9,2) +':'+SUBSTRING(@InputDateFormat,11,2) +':' + RIGHT(@InputDateFormat,2))

-- Change type to DateType
select @ConvertedDateTime = CONVERT(DATETIME,@NormalFormatDateTime,20)

-- Display the result
select @ConvertedDateTime 

Comments

[Jeremy]

This appears to sort of work except all of my values are returning as "2013-10-20 21:57:30.000" This table has 320 records and each one has a different DateTime Value. Where the VarCharDate is declared as 20131020215735 I changed the value to dbo.surveys.survey_dat hoping that each individual value would update. I'm returned with an error message that states "The multi-part identifier 'dbo.surveys.survey_dat' coudl not be bound."

[Jeremy]

The issue with this solution is that it updates all of the records with a static value: 2013-05-15 10:30:00.000. I need to update 320 records, each with it's own unique value.

[Hart CO]

The above is just for testing, you take the logic above and use it in your script, see update.