TSQL issues with view and data operations

Question

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.

Answer 1

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.

Answer 2

Aside from the error message, your date logic is incorrect for various reasons

1

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

Gives me 1st June 2019

It calculates the number of whole months between 1899-12-20 and (today) 2019-05-28 which gives 1433, and then adding that many months on to day 0 (which it implicitly understands as 1900-01-01) - yielding 1st June 2019 - 3 days in my future

-12 by implicit conversion is day -12, i.e. 12 days before 1900-01-01.

This query will always give you the 1st day of the next month after today.

2

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

Leaving aside problem #1, assuming you were really asking for period_date > '2018-06-01'

That's going to select period_dates which are greater than 1st June 2018. And since your period dates are explicitly set to be the 1st of the month

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

That'd select period_date values from 2018-07-01 (July) onwards

3

Given #1,

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

is selecting data from period_date earlier than 2019-06-01.

i.e. it is indeed selecting data from older than a year ago as stated, but it's also selecting data from the past year as well.