Problem with finding datetime entries which are related to today in a SQL Server table

Question

I have a table with a varchar column with different values in it. Some of the values in this column are in fact datetime stamp but written in varchar data type. I wish to find entries which contain datetime AND are related to today. So, my table is like this:

  ID  |  column_x
---------------------
  12  |  apple
  13  |  25.03.2018 14:13:58
  14  |  05.10.2020 10:43:17
  15  |  3620

The following query works

select [ID] ,[column_x],
CAST(CONVERT(DATETIME, [column_x] , 104) AS DATE) the_date
from [my_DB].[dbo].[my_table]
where (ISDATE([column_x])=1)

The result is like this:

  ID  |       column_x          |  the_date
-------------------------------------------------
  13  |  25.03.2018 14:13:58    | 2018-03-25
  14  |  05.10.2020 10:43:17    | 2020-10-05
  15  |  3620                   | 3620-01-01

Now I want to expand the afformentioned query to find the entries that belong to today (2020.10.05)

The following query gives error:

select * from (
select [ID] ,[column_x],
CAST(CONVERT(DATETIME, [column_x] , 104) AS DATE) the_date
from [my_DB].[dbo].[my_table]
where (ISDATE([column_x])=1) 
) s
where
(select CAST(CONVERT(DATETIME, s.[column_x] , 104) AS DATE))=
(SELECT CAST(GETDATE() AS DATE))

The error message is:

Conversion failed when converting date and/or time from character string.

I can't understand why I get this error while I have already chosen only entries that are datetime according to the SQL itself.

More strangely, the following query works fine and prints the output:

if
(select CAST(CONVERT(DATETIME, '05.10.2020 19:46:19' , 104) AS DATE))=
(SELECT CAST(GETDATE() AS DATE))
print 'condition is fulfileld'

Even replacing the date with the problematic number (3620) doesn't result in error. Just the condition is not met in that case.

I can't understand why I get that error.

Answer 1

You can use try_convert() . . . but why not convert the current date to the same format:

select [ID] ,[column_x]
from [my_DB].[dbo].[my_table]
where left(column_x, 10) = convert(varchar(10), getdate(), 104)

To do the comparison the other way:

where try_convert(date, column_x, 104) = convert(date, getdate())