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
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:
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.