Filter Year and Month when the datatype is varchar-CodePudding

I have a table with 4 columns.

I'm trying to run a query with a WHERE condition to filter out data by year and month but the issue is that the CreateDate column is of type varchar. I cannot change the data type in the table.

How can I use this query?

Am I casting it incorrectly? Do I need to Convert then apply YEAR and MONTH?

I'm not sure what to do.

This is the error I get from SQL Server:

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

Code:

SELECT * 
FROM Table1
WHERE 
    YEAR(CAST(SUBSTRING(CreateDate, 1, 4) AS datetime))  = 2021 
    AND MONTH(CAST(SUBSTRING(CreateDate, 6, 2) AS datetime)) = 2 
    AND

Column datatypes:

ID = INT
FirstName = varchar
Phone = int
CreateDate = varchar

CodePudding user response：

You are using CAST function incorrectly e.g. attempting cast('02' as datetime) will throw the specified error. Considering that the date is stored as string, you can use one of the following options (sorted by expected performance):

WHERE CreateDate LIKE '2021-02-%'
WHERE SUBSTRING(CreateDate, 1, 4) = '2021' AND SUBSTRING(CreateDate, 6, 2) = '02'
WHERE CAST(CreateDate AS DATE) >= '2021-02-01' AND CAST(CreateDate AS DATE) < '2021-03-01'

CodePudding user response：

The issue is due to nulls in your field as you might already know. This is how to fix it.

There are two approaches

USING ISNULL()

SELECT *  
FROM Table1  
WHERE Year(cast(ISnull(CreateDate,'1753-01-01 00:00:00') as datetime))=2021
--'1753-01-01 00:00:00' is the min date for datetime. ISNULL replaces NULLS with given value

USING Where Clause

SELECT * 
FROM Test
WHERE 
  Createdate is not null and 
  Year(cast(CreateDate as datetime))=2021