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.