CODEWITHSUNDEEP
sql-serverdatetype-conversion
Johnny Bones
Johnny Bones

Reputation: 8402

convert a date from a string into a new date field

I've got a table I imported from Excel into SQL Server 2012 that refuses to import as a date field. It comes in as a VarChar that looks like "03/15/2017". So what I did was create a new Date field in the table, and now I just want to fill that field with the Date version of the text.

I wrote this little Update code to do this:

update [dbo].[Sheet5$]
set TDate = CAST(SUBSTRING([Date], 7, 4) + '/' + SUBSTRING([Date], 1, 2) + '/' 
    + SUBSTRING([Date], 4, 2) AS DATETIME)

It's giving me an error:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Can anyone tell me what I'm doing wrong, and how to fix it so I can get these dates into a date field?

Upvotes: 2

Views: 200

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

If 2012+ use try_convert() in concert with format(). It will not throw an error if you have bogus or malformed data.

Example

Declare @YourTable Table ([Date] varchar(50))
Insert Into @YourTable Values 
 ('03/15/2017')
,('3/15/2017')
,('3/7/2017')
,('22/7/2017')

Select *
      ,AsDate   = try_convert(date,[Date]) 
      ,AsString = format(try_convert(date,[Date]),'MM/dd/yyyy')
 From @YourTable

Returns

Date        AsDate      AsString
03/15/2017  2017-03-15  03/15/2017
3/15/2017   2017-03-15  03/15/2017
3/7/2017    2017-03-07  03/07/2017
22/7/2017   NULL        NULL

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

You have some invalid data somewhere in your table. You could use TRY_CONVERT or TRY_PARSE instead of CAST if you are on 2012 or later. You also don't need the / characters in there. The ANSI supported format is YYYYMMDD. 

update [dbo].[Sheet5$]
set TDate = TRY_CONVERT(SUBSTRING([Date], 7, 4) + SUBSTRING([Date], 1, 2) + SUBSTRING([Date], 4, 2) AS DATETIME)

Then you can look at the rows where TDate IS NULL and figure out why those rows are invalid and fix the data.

Upvotes: 1

Related Questions