Menu
I'm trying to transfer some old SQL Server data using Excel into SQL Server. It seems that Import/Export Data application automatically sets most data columns to NVARCHAR(255). Problem I have, is one of my columns is supposed to be a DATE type, but all data in it looks like this 18.08.2000 14:48:15.
So, when I try to use this query:
SELECT CONVERT(Date, DATE_TIME, 113)
FROM someTable
I get this error:
Msg 9807, Level 16, State 0, Line 1
The input character string does not follow style 113, either change the input character string or use a different style.
None of the [styles] from CAST and CONVERT (Transact-SQL) are working in my case.
Any advise or help is greatly appreciated.
SOLVED:
UPDATE myTable
SET columnName = CONVERT(NVARCHAR(255),CONVERT(SMALLDATETIME, columnName,105))
ALTER TABLE myTable
ALTER COLUMN columnName SMALLDATETIME
611
As your data is nvarchar there is no guarantee it will convert to datetime (as it may hold invalid date/time information) - so a way to handle this is to use ISDATE which I would use within a cross apply. (Cross apply results are reusable hence making is easier for the output formats.)
nvarchar(255) (in SQL Server) stores 255 Unicode characters (in 510 bytes plus overhead).
Syntax: SELECT CONVERT(<DATA_TYPE>, <VALUE>); --DATA_TYPE is the type we want to convert to. --VALUE is the value we want to convert into DATA_TYPE. Example: SELECT 'Weight of Yogesh Vaishnav is ' + CONVERT(NVARCHAR(20), weight) AS person_weight FROM person WHERE name = 'Yogesh Vaishnav';
Another method that is described below with code examples can be used to tackle the same issue Sql Convert Varchar To Date. SELECT convert(datetime, '09/10/2019', 101); SELECT CONVERT(date, getdate()); Through many examples, we learned how to resolve the Sql Convert Varchar To Date problem.
As far as I can tell - style no. 103 (British/French) should work - no?
DECLARE @input NVARCHAR(255)
SET @input = '18.08.2000 14:48:15'
SELECT CONVERT(DATETIME, @input, 103)
Gives me the output of:
2000-08-18 14:48:15.000
which seems pretty reasonable, no??
73
So, if it will ever become useful to anyone, this is the exact code that changes datatype NVARCHAR to DATETIME:
UPDATE myTable
SET columnName = CONVERT(NVARCHAR(255),CONVERT(SMALLDATETIME, columnName,105))
ALTER TABLE myTable
ALTER COLUMN columnName SMALLDATETIME
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With