Date-Querying A View v/s Same in Table [Azure Synapse DB]

Question

I have an Azure Synapse DB that contains a view VW_REF_DATES. This view contains a column called c_date of type datetime2(0)

I want to be able to query this view with a Date condition in the WHERE clause using that c_date column but it keeps giving me datatype related errors ("Conversion failed when converting date and/or time from character string."). Also note that querying this view works fine with WHERE clause made up on other columns that are of type, say, varchar(500)

If I convert this same view into a Table and then use the same query that failed using a view, it works ok. Is this a limitation only with Azure Synapse DB and not with MS SQL Server DB?

I need to be able to use the View to query data by using the c_date column in the WHERE clause. For e.g.

SELECT  * FROM [dbo].[VW_REF_Dates] DATES 
WHERE FORMAT(DATES.[c_date], 'yyyy-MM-dd') <= '2022-03-20'

OR

SELECT  * FROM [dbo].[VW_REF_Dates] DATES
WHERE FORMAT(DATES.[c_date], 'yyyy-MM-dd') <= '2022-03-20'
       AND FORMAT(DATES.[c_date], 'yyyy-MM-dd') > '2021-03-19'

Using the view, I have tried all of the below and none of them work (but it works if I convert the view into a table) -

SELECT  * 
FROM [dbo].[VW_REF_Dates] DATES 
WHERE YEAR(DATES.[c_date]) <= '2022'

(OR ANY OF THE BELOW)

WHERE DATES.[c_date] <= '2022-03-20 00:00:00.000'

WHERE FORMAT(DATES.[c_date], 'yyyy-MM-dd') <= '2022-03-20'

WHERE CONVERT(date, DATES.[c_date]) <= '2022-03-20 00:00:00.000'

I was expecting it to work and give me the relevant records as it does when querying the table. Instead, I keep getting errors such as below every time-
Msg 241, Level 16, State 1, Line 14
Conversion failed when converting date and/or time from character string.

Edit1

Here's how the view is designed-

CREATE VIEW [dbo].[VW_REF_Dates]
AS SELECT  CASE 
        WHEN CONVERT(DATETIME2(0), Att_5, 103) IS NOT NULL THEN CONVERT(DATETIME2(0), Att_5, 103)
        WHEN CONVERT(DATETIME2(0), Att_5, 101) IS NOT NULL THEN CONVERT(DATETIME2(0), Att_5, 101)
      END AS [c_date]
    , Att_6 AS [num_days]
FROM [dbo].[tbl_Ref]

Some sample values for Att_5 and Att_6 as below -

Att_5       Att_6
22/06/2006  NULL
21/06/2009  364
28/06/2012  338
24/06/2013  316

Answer 1

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

When a query tries to convert a string that contains a date or time data type value but fails because of improper formatting or other problems, below are some reasons:

• The string value that is being converted does not have the date or time value's expected format. For instance, the conversion will fail if the string contains "dd-mm-yyyy" while the desired format is "yyyy-mm-dd."
• The string value contains characters that are not valid for a date or time value, also check if null or empty values are not there.
• Views don't store data themselves; they are essentially saved queries that are executed each time the view is queried this can cause in datatype conversions.

To resolve it check the Data type is correct, and the data is in correct format of your view/ table.

Is this a limitation only with Azure Synapse DB and not with MS SQL Server DB?

I tried in my environment it worked fine for me here my sample queries and data:

CREATE TABLE dbo.REF_DATES (
id INT,
c_date DATETIME2(0),
description VARCHAR(100));

-- Create a view based on the table
CREATE  VIEW [dbo].[VW_REF_Dates1] AS
SELECT id AS [Active_Flag],
CASE  WHEN  CONVERT(DATETIME2(0), c_date, 103) IS  NOT  NULL  THEN  CONVERT(DATETIME2(0), c_date, 103)
WHEN  CONVERT(DATETIME2(0), c_date, 101) IS  NOT  NULL  THEN  CONVERT(DATETIME2(0), c_date, 101) END  AS [c_date1] FROM [dbo].[REF_DATES]

SELECT * FROM [dbo].[VW_REF_Dates1] AS DATES
WHERE  CONVERT(date, DATES.[c_date1]) <= '2022-03-20 00:00:00.000'

Check your data type is converted correctly.