Sql-server – TSQL issues with view and data operations

sql servert-sql

I can't get my head around this sooo – help! 😉

Working with SQL Server here.

I have a few tables that hold customer sales, products, customer and other data imported from SAP.

I have create a view that combined all this data into a single easy to read "table". The only caveat being the data coming from SAP for each sale is group by sale period of month.year (09.2018, 10.2018, 11.2018, etc).

My import script break this data into two columns, month & year.
Now, because I will have to run queries with date function on this view I needed a datetime field, so in the view definition I essentially did this:

CONVERT(datetime, CONCAT(a.period_year,'-',a.period_month,'-01')) AS period_date

first create a string value of the date adding day (01) and then convert this to datetime type.

This view will not hold any future data, but for the sake of argument & testing I run this query:

SELECT * FROM my_sales_view WHERE period_date <= getdate();

I get all records returned to me, no problem.
But, let say I need all data from past 12 months, from entire month, meaning if I run the query on 15th of May 2019 I need records from 1st of June 2018, not 16 of June.

This will give me that date:

SELECT DATEADD(month, DATEDIFF(month, -12, getdate()), 0)

so, I though by running this I would get what I want:

SELECT * FROM my_sales_view WHERE period_date > DATEADD(month, DATEDIFF(month, -12, getdate()), 0)

But I don't. Instead the query returns the following error:

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

However when I run this:

SELECT * FROM my_sales_view WHERE period_date < DATEADD(month, DATEDIFF(month, -12, getdate()), 0)

by just inverting the operator and getting the date older then 1 year ago it works fine!

So, what gives?! This makes no sense to me, what am I missing here.

Best Answer

Sounds like you have at least one row where the combination of year/month is not valid (and only with certain plans is the conversion attempted before that row is filtered away). To be certain, we should see the full definition of the view, the actual plan for the query that works, and the estimated plan for the query that fails (if you can get it).

First, I suggest that instead of breaking the string from SAP apart into separate components, you import the data into the correct data type from the start. But that's a different story. Given what you have, I would identify the bad rows this way:

SELECT [key?], period_year, period_month
  FROM dbo.[table shown as "a" in my_sales_view]
  WHERE TRY_CONVERT(datetime,CONCAT(period_year,
    RIGHT('0'+RTRIM(period_month),2),'01')) IS NULL;

And when you find the bad rows and fix them, your current view will work, but if you don't make any other changes, this will just be temporary and bad data will happen again. So your view can say the following instead of all this CONCAT nonsense:

TRY_CONVERT(datetime,CONCAT(a.period_year,
  RIGHT('0'+RTRIM(a.period_month),2),'01'))) AS period_date

Of course this will mean that no matter what predicate you apply to period_date, those bad rows won't appear in the output. Hopefully you wouldn't expect them to, but without the error, it won't be obvious those rows are missing.

Related Question