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

Question

I have a varchar column in my database called ref003 which stores datetime like the below

2021-04-04 20:01:03

Here, date format is like yyyy-MM-DD

When I execute the below select query I am getting error

SELECT *
FROM FileIndex
WHERE CAST(CONVERT(CHAR(30), CONVERT(DATETIME, Ref003, 105), 101) AS DATE) 
    BETWEEN CAST(CONVERT(CHAR(10), CONVERT(DATETIME, '01-01-2021', 105), 101) AS DATE) 
    AND CAST(CONVERT(CHAR(10), CONVERT(DATETIME, '31-12-2021', 105), 101) AS DATE)

And the error is

Msg 241, Level 16, State 1, Line 5 Conversion failed when converting date and/or time from character string.

What is the problem here and how can I solve this problem?

Answer 1

First, for the conversion you need to convert to VARCHAR, not DATE. Note this expression:

CONVERT(VARCHAR(10), CAST(<your date value> AS DATE), 20)

With that in mind, you can clean your query up like so:

--==== Easily consumable sample data
DECLARE @thetable TABLE (someid INT, thedate DATETIME);
INSERT @thetable
VALUES(1,'2021-04-04 20:01:03'),(2,'2021-06-04 22:01:05'),(1,'2021-04-29 10:31:11');

--==== Solution
SELECT      t.*, FormattedDate = fmt.Dt 
FROM        @thetable                                               AS t
CROSS APPLY (VALUES(CONVERT(VARCHAR(10), CAST(t.thedate AS DATE), 20))) AS fmt(Dt)
WHERE       t.thedate BETWEEN '20210401' AND '20210501';

Returns:

someid      thedate                 FormattedDate
----------- ----------------------- -------------
1           2021-04-04 20:01:03.000 2021-04-04
1           2021-04-29 10:31:11.000 2021-04-29