Copy link to clipboard
Copied
Hi,
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 amd DateToDD and they are 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 compare to see if it falls within the current date using GETDATE(). Our results are never accurate unfortunately. Any help appreciated.
Copy link to clipboard
Copied
tribule wrote:
Appreciate the reply, but I now get: "Error in list of function arguments: 'AS' not recognized." You may want to create a small table and try it.
Yes, I think it needs one set of outer brackets. I get the feeling we're almost there.
WHERE
GETDATE() >= CAST((CAST(DateFromYYYY AS varchar(4)) + '-' + CAST(DateFromMM AS varchar(2)) + '-' + CAST(DateFromDD AS varchar(2))) AS DATETIME)
AND
GETDATE() <= CAST((CAST(DateToYYYY AS varchar(4)) + '-' + CAST(DateToMM AS varchar(2)) + '-' + CAST(DateToDD AS varchar(2))) AS DATETIME)
Copy link to clipboard
Copied
BKBK, the query syntax is now ok and I get back data, but it's the wrong data again. For example, I have this record being returned:
2016 01 21 -> 2016 01 26
however the 26th was yesterday, where I am, and so this record should not appear. This was the problem I always had and my original SQL query was almost identical to yours now. Most annoying, I think my SQL driver is playing up,
Copy link to clipboard
Copied
GetDate() has a time part. Whereas the result you've shown (2016 01 21 -> 2016 01 26) hasn't. So let's truncate the time, so that we can compare like with like:
WHERE
CONVERT (date, GETDATE()) >= CAST((CAST(DateFromYYYY AS varchar(4)) + '-' + CAST(DateFromMM AS varchar(2)) + '-' + CAST(DateFromDD AS varchar(2))) AS DATETIME)
AND
CONVERT (date, GETDATE()) <= CAST((CAST(DateToYYYY AS varchar(4)) + '-' + CAST(DateToMM AS varchar(2)) + '-' + CAST(DateToDD AS varchar(2))) AS DATETIME)
Copy link to clipboard
Copied
That result is just me typing it out btw. I amended the query and it ran, same result. I'm getting records whose To date has passed
Copy link to clipboard
Copied
tribule wrote:
I'm getting records whose To date has passed
That's why I tried to get you to run the query I posted Jan 25, 2016 1:22 PM. It will allow you to see the details of the conversion and the comparison.
You said it has syntax errors but I have just confirmed that it does not have syntax errors as written. If you modified it then you probably introduced the error.
If you provide details of the actual query you tried to run and the actual text of the error message then we can try to help you, otherwise we are just shooting in the dark.
Cheers
Eddie
Copy link to clipboard
Copied
tribule wrote:
That result is just me typing it out btw.
I realized that, hence my suggestion to compare with a date, not with a datetime. Unfortunately, I am unable to test, as I am not on MS SQL at the moment.
Here is yet another variation on the theme:
WHERE
date(GETDATE()) >= date(CAST((CAST(DateFromYYYY AS varchar(4)) + '-' + CAST(DateFromMM AS varchar(2)) + '-' + CAST(DateFromDD AS varchar(2))) AS DATETIME))
AND
date(GETDATE()) <= date(CAST((CAST(DateToYYYY AS varchar(4)) + '-' + CAST(DateToMM AS varchar(2)) + '-' + CAST(DateToDD AS varchar(2))) AS DATETIME))