Help with date query needed

[tribule]

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.

[BKBK]

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)

[tribule]

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,

[BKBK]

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)

[tribule]

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

[EddieLotter]

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

[BKBK]

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))