We have a database with dates stored as day, month and year fields and need to build a query that returns a recordset for only those dates that fall within the current date. For some reason the query returns records that are always a day too late or a day too early. Has anyone got a better way of writing this query?
We are on MSSQL 2008r2. The query we have so far is:
select * from mydatabase where DATEADD( day, -1, CAST(CAST(DateFromYYYY AS varchar) + '-' + CAST(DateFromMM AS varchar) + '-' + CAST(DateFromDD + 1 AS varchar) AS DATETIME) ) <= GETDATE() AND DATEADD( day, -1, CAST(CAST(DateToYYYY AS varchar) + '-' + CAST(DateToMM AS varchar) + '-' + CAST(DateToDD AS varchar) AS DATETIME) ) >= GETDATE()
The fields we have are DateFromYYYY, DateFromMM amd DateFromDD and DateToYYYY, DateToMM and DateToDD and they are all varchars. We can't change the fields in the database as this is a legacy system. Basically we build each date string on-the-fly and then cast it to a DATETIME, and then compare to see if it falls within the current date using GETDATE(). Our results are never accurate unfortunately.
We have tried without the CAST, as it has been pointed out that we are casting varchars back to varchars, but without the CAST we get no results at all. Any help appreciated.