If you store a date time value as string into a varchar column and would like to perform T-SQL to query it like a normal datetime column, here is how you can achieve it:
For instance, assuming that the varchar column (varchar(50)) which a date time string is store is called 'cAttribute6' and the table name is called TBL_People, the following T-SQL will allow you to query the varchar column like a datetime.
SELECT * FROM TBL_People where convert(datetime,cAttribute6,103)>='2014-12-11 18:00:00'
Nonetheless, storing date time string to a varchar column comes with a risk - when the date time string is corrupted, performing the above T-SQL will result in the following exception:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
This exception message implies that it wasn't able to convert the date time string into a valid date time.
This is the result of corrupted date time string in one or multiple records of the same column. From my experience, it doesn't cause by NULL or empty string value in the column.
Valid date time string for MSSQL:
Invalid date time string: '01/01/1900 00:70:80'
The solution is to clean up the data by identifying which record has corrupted date time string. To do that, there are two methods:
1.) Select all the distinct value of the column and sort them accordingly and then go through each distinct value to identify which date time string is potentially corrupted.
SELECT distinct(cAttribute6) FROM TBL_People order by cAttribute6
Whilst doing this may be time consuming when there are too many distinct value, the second method is to create a T-SQL which will go through each value and perform ISDATE to identify valid or invalid date.
Here is the script which I did to check the column for invalid date time string.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO -- the date time format for cAttribute6 is dd/mm/yyyy HH:mm:ss set dateformat dmy declare @query nvarchar(max), @mainCursor cursor set @query = " set @cursor = cursor for SELECT cAttribute6 FROM [TBL_People]" + " WHERE cAttribute6 is NOT NULL AND cAttribute6<>''" + " ORDER BY cAttribute6" + " open @cursor" exec sp_executesql @query,N'@cursor cursor output',@mainCursor output declare @cAttribute6 as varchar(50) fetch next from @maincursor into @cAttribute6 while (@@fetch_status <> -1) BEGIN if ISDATE(@cAttribute6)=0 print 'Invalid: ' + @cAttribute6 else print 'Valid' fetch next from @maincursor into @cAttribute6 END
Take note that this T-SQL also uses another function 'set dateformat' which is necessary for ISDATE to function properly.
In this case, the dateformat is set to dmy, which means that the data which my date time string were is in the format of dd/MM/yyyy HH:mm:ss
Nonetheless, if your data is in the format of MM/dd/yyyy HH:mm:ss, then you should use 'set dateformat mdy' and so on so forth.
I have tested this and it will work!