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.
Please read this post:
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.
In SQL Server 2012, date takes 3 bytes while datetime takes 8 bytes.
If your table has millions of millions of rows, then yes, then the IO and logical page size should be reduced and your performance will be quicker.
First though i'd suggest to try dumping the table into a testing table, convert the column, apply your indexes and benchmark using real life workloads against this table using the original as a baseline.
Best Answer
You can use TRY_PARSE for this. This function will attempt to parse a supplied NVARCHAR value as a given data type, and if it fails it returns NULL.
For example, you can try the following:
and it returns
But this
returns NULL because it cannot parse 'abc' as a datetime value. There are other options, such as using TRY_CONVERT or TRY_CAST, however, TRY_PARSE as the advantage of allowing you to use a culture setting to parse values for a specific region/language.
For example,
TRY_PARSE('11-23-09' AS DATETIME2)will parse if the default region is en-US on your SQL Server, butTRY_PARSE('23-11-09' AS DATETIME2)will fail as the NVARCHAR value is not using the US date format.Specifying the region in the TRY_PARSE function -
TRY_PARSE('23-11-09' AS DATETIME2 USING 'en-AU')- means that the string value will PARSE correctly.You can see this behaviour in this db<>fiddle.
For your specific requirement, use TRY_PARSE in the WHERE clause to identify rows in the first table that can be successfully converted to a datetime value for inserting into your second table.