4

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.

  • Do you have any problem with creating a new column, removing the old one and renaming the new column ? Seems to be the best solution. – DeadEye May 6 '15 at 8:15
  • "I need to keep the same format" SQL Server will store the value in whatever internal binary representation it thinks best. If you need it in DD/MM/YYYY format that is a job for the UI, report generator or whatnot. – Michael Green May 6 '15 at 11:55
  • I don't have any problem of altering table I have full access and not have a problem about date format. Only thing I need is the data already stored in table – wajira000 May 10 '15 at 5:33
3

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';
  • In this command UPDATE X SET TempDate = PARSE(Date AS datetime USING 'it-IT');. my tempdate is underline in a red color and after I run the query it show rows are affected but nothing copy to new column. Sorry for my poor knowledge – wajira000 May 10 '15 at 6:01
4

As you mention you string value format is in 21/11/2105 then you can try following one

Alter table table name alter column column name datetime NULL

( null is optional if you want to allow null value to your field).

Note that if the column contain string value like "abc" (other than date format value) then it will show error as conversion fail.

  • It's not working same error. Msg 242, Level 16, State 3, Line 1 The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. – wajira000 May 10 '15 at 5:33
  • Check your data in the column field , if it have other than the format 21/11/2014(as you mentioned in the question) then it will work. otherwise it will give out of range error – Rojalin Sahoo May 10 '15 at 8:49
  • did you get your solution? @wajira000 – Rojalin Sahoo May 11 '15 at 5:10
  • Thank you. Your solution is working. I found that I have a different format of date include in the table. Because dates import from file. So I cant use your solution in production. Anyway thanks for the help – wajira000 May 11 '15 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.