Trying to automate a process by using this code for the DATE criteria in a view:
BETWEEN CONVERT (varchar, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 15), 112)
AND CONVERT (varchar, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 14), 112)
however, the view comes to a grinding halt! If I specifically use
BETWEEN 20111016 AND 20111115
for the date criteria then the view displays the results perfectly, however I do not want to edit the date values on a monthly basis so is there another way of coding for those dates that SQL 2005 will digest easily?
The date is stored as decimal, for example "20111117", not as DATETIME Datatype.
I have since discovered that the View is running that code for the entire set of records in the source table, and not just the records I want to return as a result. I did try to use Variables for the criteria and it worked perfectly, but I can't save that as a view as I had hoped.
Creating a SPROC for it would be an option if only it allows an Access User who does not have SQL 2005 installed on their machine the ability to kick the sproc into action. I'm not sure if possible or how to go about doing this.
I shall try 'DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 15), ' as the criteria.