CODEWITHSUNDEEP
sqlsql-server-2012
user5245950
user5245950

Reputation:

Date Format Issues SQL Server 2012

I have an application which imports a DAT file into some SQL Server tables.

The DAT file sometimes contains incorrect date formats.

Example: 

DateCreated = 000100893

The DateCreated column in SQL Server is a datetime type.

The application fails when importing the data due to incorrect format and I have to manually null out these values so I can re-import.

Is there a way in SQL to have restrictions on the datetime column? For example, if the data is not in the correct format, automatically null out the column? I cannot change the datetime datatype for that column because most of the time the dates are correct and I am using this column for other calculations.

Upvotes: 0

Views: 89

Answers (2)

Mincong Huang
Mincong Huang

Reputation: 5552

According to TRY_CONVERT (Transact-SQL), you can do :

 SELECT TRY_CONVERT(datetime2, '000100893') AS Result;

Upvotes: 0

Code Different
Code Different

Reputation: 93181

You are looking for the TRY_PARSE function:

SELECT TRY_PARSE(DateCreated AS datetime)

It returns null if DateCreated can't be cast into datetime

Upvotes: 2

Related Questions