Search code examples
sql-servert-sqldatetimevarchar

Most efficient way to compare datetime to varchar

I'm working with a third party Microsoft SQL Server database, where most dates are saved as varchar columns in the format yyyy_MM_dd hh:mm:ss.SSS. In a lot of reports I need to select a specific time frame defined by two datetime variables. I'm using the following statement:

where cast(replace(a.somedate,'_','-') as datetime) between @begin and @end

I noticed that with this statement the query gets expensive fast when a lot of records need to be processed. What's the most efficient way to compare a varchar to a datetime?

Solution

  • Since this is a vendor application and you don't have control over the schema, you can refactor the WHERE clause as below for a sargable expression. This will allow an index on somedateas to be used efficiently. For example:

    WHERE a.somedateas BETWEEN REPLACE(CONVERT(varchar(19), @begin, 120), '-', '_') AND REPLACE(CONVERT(varchar(19), @end, 120), '-', '_')