Google Analytics

Search This Blog

Friday, December 26, 2014

T-SQL: Querying a String Column as Date

This article applies to and experimented on MSSQL 2008 R2.

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.

For instance:

Valid date time string for MSSQL:

'01/01/1900 00:00:00'

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.

http://msdn.microsoft.com/en-us/library/ms187347.aspx

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!

No comments: