SQL Server – Changing an NVARCHAR Column to DATETIME

sql serversql server 2014type conversion

I am new to SQL Server and I have a problem.

I created a database to store dates in an nvarchar field but now I need to convert that field to datetime. I searched the internet and couldn't find a proper solution.

One thing I found was to create a new datetime column, copy string value to that, then rename the new column to original one.

My string value format is 21/11/2014. I need to keep the same format. If anyone could give me a SQL Server query that can do that, I'd highly appreciate. I'm using SQL Server Management Studio 2014.

Best Answer

As you probably have already found out, dates should be stored as dates and not as their human readable representation (varchar).

In order to convert from string to date, you have to use the conversion functions available in your version of SQL Server (which seems to be 2014 if it is the same as Management Studio). In this case, you can use the PARSE function.

Example:

SELECT PARSE('21/11/2014' AS datetime USING 'it-IT')

You can read more about date to string and string to date datatype conversion in this article: http://www.sqlservercentral.com/articles/T-SQL/88152/

Back to your issue, add a new datetime column, for instance newCol, update the values parsing the strings, drop the old column and rename the new column to the original name:

ALTER TABLE YourTable ADD newCol datetime NULL;
UPDATE YourTable SET newCol = PARSE(oldCol AS datetime USING 'it-IT');
ALTER TABLE YourTable DROP COLUMN oldCol;
EXEC sp_rename 'YourTable.newCol', 'oldCol', 'COLUMN';

Related Solutions

SQL Server – Convert varchar Column to datetime

Assuming dates in other months are truncated to 3-char months:

 ALTER TABLE dbo.tablename ADD newcol DATETIME;

 UPDATE dbo.tablename
   SET newcol = CONVERT(DATETIME, 
    REPLACE(LEFT(varchar_col, 11), '/', ' ') 
        + ' ' + RIGHT(varchar_col, 8), 113);

Once you've verified, you can drop the varchar_col, rename newcol, and then re-add any affected indexes.

SQL Server – Understanding Datetime Formats

The only truly safe formats for DATETIME/SMALLDATETIME in SQL Server are:

yyyyMMdd
yyyyMMdd hh:nn:ss[.mmmmmmm]
yyyy-MM-ddThh:nn:ss[.mmmmmmm]
----------^ yes, that T is important!

Anything else is subject to incorrect interpretation by SQL Server, Windows, the provider, the application code, end users, etc. For example, the following always breaks:*

SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2013-11-13');

Result:

Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line 2
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Just changing the language (which any of your user sessions can do) forced SQL Server to interpret that as YYYY-DD-MM instead of YYYY-MM-DD. Similar things can happen with setting like DATEFORMAT. But these settings are literally ignored when using the above two formats.

Always, always, always use one of the above two formats. If you are passing a variable as a string, stop doing that. If you can't, check to make sure it passes ISDATE() first. If you are letting people type any date string into a form field, stop doing that, too. Use a date-picker or calendar control and dictate the format of the string before you pass it to SQL Server. Well, depending on the language, just keep it as a datetime value and don't convert it to a string at all.

I can't reproduce your problem at all, btw...

Please read this post:

Bad Habits to Kick : Mis-handling date/range queries

_{There is an exception: SELECT CONVERT(DATE, 'yyyy-mm-dd'); will not break. But I err on the side of consistency rather than using a format only in the one place where I know it doesn't break, and having to use a safer format everywhere else.}

Best Answer

Related Solutions

SQL Server – Convert varchar Column to datetime

SQL Server – Understanding Datetime Formats

Related Question