Computed column

In curre开发者_运维技巧nt table I have a column that holds the date field in ddmmyyyy format and it is of type varchar(8). The column has some string value also. I want to create a computed column that will hold the value in DateTime format if the value in source column is valid date time.

Assuming your varchar(8) column is dateString :

Cast([dateString] as datetime)

SQL Server prefers dates in the format of yyyymmdd so there will be some string manipulation involved to format your data like this. We should also use the IsDate function to make sure we have a valid date.

So:

Cast(Case When IsDate(Right(@Data, 4) 
     + SubString(@Data, 3, 2) 
     + Left(@Data, 2)) = 1 
        Then Right(@Data, 4) 
           + SubString(@Data, 3, 2) 
           + Left(@Data, 2) End  As DateTime)

Notice that this code should correctly handle invalid dates contained within your varchar column. If a date is invalid, this code will return NULL.

try parsing your varchar into dd/mm/yyyy before attempting the cast:

cast(substring([datestring],1,2) + '/' + 
substring([datestring],3,2) + '/' + 
substring([datestring],5,4) as datetime)