I need to pass SQL to a third party app as a complete string, without the ability to change variables. The results will be data for a report. The requirements for the report are any invoicedate (datetime) within the past six months from the end of last month. I have come up with the following, but would greatly welcome an easier and shorter way in SQL..
WHERE invoicedate between
(SELECT Convert(datetime, Convert(varchar, DateAdd(mm, -6, DateAdd(dd, 1 - DatePart (dd, GetDate()), GetDate())), 101), 101))
and
(SELECT Convert(datetime,Convert(varchar, DateAdd(dd, 1 - DatePart (dd, GetDate()), GetDate()), 101), 101))
MISfIT