Convert varchar column to datetime in sql server

Question

I have a table with following definition and data.

Definition:

CREATE TABLE [dbo].[TestTB]
(
    [CREATEDATE] [nvarchar](50) NULL
) ON [PRIMARY]

Data:

 10/9/2014 
 1/26/2015 
 2/16/2015

When I run the query:

Select 
    CAST(CREATEDATE AS DATETIME) as CREATEDATE 
FROM 
    [dbo].[TestTB]

It is throwing error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

The above does not work even after running

SET DATEFORMAT dmy

However the following query works fine

  DECLARE @data nvarchar(50)
  SET @data =  ' 10/9/2014 '
  Select CAST(@data as DateTime)

Above query returns: 2014-10-09 00:00:00.000

How do I convert a date string (of mm/dd/yyyy format) stored in a column to datetime?

Use `convert()`: https://msdn.microsoft.com/en-us/library/ms187928.aspx. — Gordon Linoff, Apr 21 '15 at 15:17
@GordonLinoff, Tried Select CONVERT(DATETIME, CREATEDATE, 110) FROM [dbo].[TestTB]. Same error. — user2520246, Apr 21 '15 at 15:26
@user2520246: so it's not possible to change it? You will save a lot of time in future, so i would take the breaking change. Your queries will also be much more efficient with the right datatype and you can't run into localization issues. — Tim Schmelter, Apr 21 '15 at 15:33
`SET DATEFORMAT dmy` would not be correct if your data has date strings like `2/16/2015` — D Stanley, Apr 21 '15 at 16:04
@user2520246 Shouldn't it be `CONVERT(DATETIME, CREATEDATE, 101)` - 101 instead of 110? — D Stanley, Apr 21 '15 at 16:05
Check out this post: http://stackoverflow.com/questions/23697936/conversion-error-when-converting-nvarchar-to-datetime-in-sql-server — FutbolFan, Apr 21 '15 at 16:45

Aasish Kr. Sharma · Accepted Answer · 2015-04-21T16:07:42.180

First, if your table column is "DateTime" type than it will save data in this format "2014-10-09 00:00:00.000" no matter you convert it to date or not. But if not so and if you have SQL Server version 2008 or above than you can use this,

DECLARE @data nvarchar(50)
SET @data =  '10/9/2014'

IF(ISDATE(@data)>0)
BEGIN
    SELECT CONVERT(DATE, @data)
END

Otherwise

DECLARE @data nvarchar(50)
SET @data =  '10/9/2014'

IF(ISDATE(@data)>0)
BEGIN
    SELECT CONVERT(DATETIME, @data)
END

To Insert into table

INSERT INTO dbo.YourTable
SELECT CREATEDATE FROM
(
    SELECT
        (CASE WHEN (ISDATE(@data) > 0) THEN CONVERT(DATE, CREATEDATE) 
        ELSE CONVERT(DATE, '01/01/1900') END) as CREATEDATE 
    FROM 
        [dbo].[TestTB]
) AS Temp
WHERE
    CREATEDATE <> CONVERT(DATE, '01/01/1900')

Appreciate your help.But my problem is how do i query this against a table column.I just want to query the table column containing the date string, convert it to a datetime value and update the date in a datetime column of the same table. — user2520246, Apr 21 '15 at 15:59

Mukund · Answer 2 · 2015-04-21T16:04:43.603

0

DECLARE @data nvarchar(50)
  SET @data =  ' 10/9/2014 '
  SELECT CONVERT(datetime, @data, 101)

SELECT CONVERT(datetime, CREATEDATE, 101) as CREATEDATE 
FROM [dbo].[TestTB]

http://www.sqlfiddle.com/#!6/f2103

please check this http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx

edited Apr 21 '15 at 16:04

answered Apr 21 '15 at 15:21

Mukund

1,679
1
11
20

Yes this works as i have mentioned in question.How do i do conversion when the date string is stored in a varchar column? – user2520246 Apr 21 '15 at 15:33
Appreciate your help. However SELECT CONVERT(datetime, CREATEDATE, 103) as CREATEDATE FROM [dbo].[TestTB] retuns the same error. – user2520246 Apr 21 '15 at 15:53
got the problem 103 was for dd/mm/yyyy but your data is in mm/dd/yyy format. 101 is correct. now check it @user2520246 – Mukund Apr 21 '15 at 16:05

FutbolFan · Answer 3 · 2015-04-21T16:11:40.400

0

Try this out:

SELECT IIF(ISDATE(CREATEDATE) = 1, CONVERT(DATETIME, CREATEDATE, 110), CREATEDATE) as DT_CreateDate
FROM [dbo].[TestTB]

SQL Fiddle Solution

edited Apr 21 '15 at 16:11

answered Apr 21 '15 at 16:01

FutbolFan

13,235
3
23
35

AiApaec · Answer 4 · 2015-04-21T16:20:55.353

nvarchar to datetime is an implicit conversion, it means you can do this:

declare @strDate nvarchar(50), @myDate datetime
set @strDate = '01/26/2013'
set @myDate = @strDate

But your problem is date format so you have to convert it, but you have to scpecify the format or style and the code of the style you're using is 101 (according to this table) so the secure way:

 declare @strDate nvarchar(50), @myDate datetime
 set @strDate = '01/26/2013'
 set @myDate = CONVERT(DATETIME, @strDate, 101)

You can skip the code but with diferent configuration(server) that code will break. If you use the ISO style (yyyymmdd) you never would'nt have to worry and the conversion would be implicit:

  declare @strDate nvarchar(50), @myDate datetime
  set @strDate = '20150421' --ISO style (yyyymmdd)
  set @myDate = @strDate

Convert varchar column to datetime in sql server

4 Answers4

To Insert into table