Date Query Issue

1

Hi
I have an issue when Querying a date.t heda te is stored as varchar.

Select * from Table where EndDate >= '01/01/2021'

It returns older dates aswell

2

You could try:
Select * from Table where CAST(EndDate AS DATE) >= '20210101'

This can slow down the query if the query plan was using an index on the EndDate column. The recommended method, if you are able to do so, would be to make the EndDate column of type DATE.

3

Managed to solve it with this

try_convert(DATE, [Enddate], 103)

4

FYI - this will not be able to use an index on the Enddate column if one exists, forcing a table/index scan. You would be much better off adding a computed column using this formula, persisting the column and indexing that column. Then you can use it directly as a date data type in your queries which much better performance.

1 Like
5

What data type is EndDate? And why?

6

OP stated it was varchar - but I agree, why is it stored that way in the first place?