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.

[EddieLotter]

To be honest, you would get much better help asking questions about SQL Server queries in a forum dealing specifically with SQL Server.

tribule wrote:

...they are varchars.

If they are already varchar, why are you casting them to varchar?

Cheers

Eddie

[tribule]

Valid point about the CAST, but it's the only way I could get it to work at all. If I remove them then there are no results. I'll leave the question here, if you don't mind, as another CF developer has surely had the same issue. MSSSQL 12 has added more date-part functions that might assist here.

[EddieLotter]

Try the following and see if SQL Server gives you a list of dates or an error:

select top 5 datefromparts(DateFromYYYY, DateFromMM, DateFromDD) as Result from mydatabase

Cheers

Eddie

[tribule]

Alas we are on MSSQL 2008r2 and there is no datefromparts() function.

[EddieLotter]

tribule wrote:

Alas we are on MSSQL 2008r2 and there is no datefromparts() function.

Time to upgrade.

Okay, try the following and see if you get valid dates:

SELECT TOP 5 CONVERT(DATE, DateFromYYYY + '-' + DateFromMM + '-' + DateFromDD, 102) as Result FROM mydatabase

Cheers

Eddie

[tribule]

That gives an error: 'int to date not allowed'. I am trying to see if the current date falls within any of the dates in the table btw, so that will need a range check I think?

[EddieLotter]

tribule wrote:

That gives an error: 'int to date not allowed'.

...and this is why I made my first comment.

tribule wrote:

I am trying to see if the current date falls within any of the dates in the table btw, so that will need a range check I think?

In theory, your query should then be something like the following:

SELECT * FROM mydatabase WHERE GETDATE() BETWEEN DateFrom AND DateTo

Please post the result of the following query so we can get an idea of what the data look like:

SELECT TOP 10 DateFromYYYY + '-' + DateFromMM + '-' + DateFromDD AS DateFrom FROM mydatabase

Cheers

Eddie

[tribule]

This statement produces a series of single column results, DateFrom, all with the values literally added up. The fields are int's btw, not varchars as I incorrectly said, sorry. The BETWEEN clause looks promising but the individual parts will surely need casting to get them into a valid date?

[EddieLotter]

tribule wrote:

The fields are int's btw, not varchars as I incorrectly said, sorry.

Ah, that explains the original cast.

tribule wrote:

the individual parts will surely need casting to get them into a valid date?

That's where we're heading. We first need to be sure we're getting a valid string version of the date before making it a date type.

Try the following and let us know the result:

SELECT TOP 10 CAST(DateFromYYYY AS varchar(4)) + '-' + CAST(DateFromMM AS varchar(2)) + '-' + CAST(DateFromDD AS varchar(2)) AS DateFrom FROM mydatabase

Cheers

Eddie

[tribule]

Ok, this works:

SELECT     TOP (10)
CAST(DateFromYYYY AS varchar(4)) + '-' + CAST(DateFromMM AS varchar(2)) + '-' + CAST(DateFromDD AS varchar(2)) AS DateFrom,
CAST(DateToYYYY AS varchar(4))  + '-' + CAST(DateToMM AS varchar(2)) + '-' + CAST(DateToDD AS varchar(2)) AS DateTo
FROM         mytable

Produces:

2016-1-21	2016-2-26
2016-1-1	2016-2-14
2015-1-1	2016-6-21
2015-1-1	2016-12-31
2015-1-1	2016-1-21
2015-1-1	2016-12-31

The casting of the date from the individual fields was not the issue originally btw.

[EddieLotter]

tribule wrote:

The casting of the date from the individual fields was not the issue originally btw.

I'm being thorough to ensure you're getting the dates you expect.

Now do the following to compare the string to the date conversion:

SELECT TOP 10 CAST(DateFromYYYY AS varchar(4)) + '-' + CAST(DateFromMM AS varchar(2)) + '-' + CAST(DateFromDD AS varchar(2)) AS DateFromAsString,
CONVERT(DATE, CAST(DateFromYYYY AS varchar(4)) + '-' + CAST(DateFromMM AS varchar(2)) + '-' + CAST(DateFromDD AS varchar(2)), 102) AS DateFromAsDate
 FROM mytable

I hope the leading space before the single digit months and days doesn't cause a problem. for your version of SQL Server.

Cheers

Eddie

[tribule]

Eddie, alas this query yields a "cannot call methods on date" error.

[EddieLotter]

Okay, use DATETIME instead.

Try the following query and see if you get the records you want:

SELECT TOP 10
  DateFromYYYY, DateFromMM, DateFromDD,
  CONVERT(DATETIME, CAST(DateFromYYYY AS varchar(4)) + '-' + CAST(DateFromMM AS varchar(2)) + '-' + CAST(DateFromDD AS varchar(2)), 102) AS DateFromAsDate,
  GETDATE() AS dateToday,
  DateToYYYY, DateToMM, DateToDD,
  CONVERT(DATETIME, CAST(DateToYYYY AS varchar(4)) + '-' + CAST(DateToMM AS varchar(2)) + '-' + CAST(DateToDD AS varchar(2)), 102) AS DateToAsDate
FROM mytable
WHERE GETDATE() BETWEEN
  CONVERT(DATETIME, CAST(DateFromYYYY AS varchar(4)) + '-' + CAST(DateFromMM AS varchar(2)) + '-' + CAST(DateFromDD AS varchar(2)), 102)
  AND
  CONVERT(DATETIME, CAST(DateToYYYY AS varchar(4)) + '-' + CAST(DateToMM AS varchar(2)) + '-' + CAST(DateToDD AS varchar(2)), 102)

Cheers

Eddie

[Tony D3]

Have you tried:

select * from mydatabase where

<cfqueryparam value="#GETDATE()# cfsqltype="cf_sql_date"> between DateToYYYY and DateFromYYYY

[BKBK]

tribule wrote:

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.

The results of the two dateAdd functions may differ. So you shouldn't expect them to occur on the same date.

Were you perhaps aiming for something like this:

where

    date(DATEADD(day, -1, CAST(CAST(DateFromYYYY etc.) ))) = date(GETDATE()) AND

      date(DATEADD(day, -1, CAST(CAST(DateToYYYY etc.) ))) = date(GETDATE())

Here the date function extracts the date part from the datetime expression.

[tribule]

I am trying to see if the current date falls inside the dates in the table, so is not a "between" query needed? I did not understand your query as it seems to compare only one date with the current. I have a range of dates in the table and need all those that the current date falls within to be returned.

[BKBK]

tribule wrote:

I am trying to see if the current date falls inside the dates in the table, so is not a "between" query needed? I did not understand your query as it seems to compare only one date with the current. I have a range of dates in the table and need all those that the current date falls within to be returned.

No, my query does not compare only one date with the current date. Did you try it?

It compares multiple dates with the current date. For example, the value of date(x) for each of the following date values is the same, namely,  2016-01-26:

                  x

2016-01-26 13:10:02.047

2016-01-26 09:05:16.235

2016-01-26 23:59:59

2016-01-26 01:01:01.001

[tribule]

I not only want the same dates, but I want to know if today's date is within the to and from date in the table. Does your query do that? Sorry but I don't understand if it does or not. If I have this data, from and to dates respectively:

2016-01-24  2016-01-28

2016-01-24  2016-01-25

2016-01-22  2016-01-24

Then if today's date is 2016-01-25 I would expect the first two rows to be returned, as today's date falls within the dates (inclusively) but the third row should not be returned, as the to date there has passed (24th was yesterday). Is this what your query does, as that is what I need? Sorry to be pedantic, I just want to make sure I'm explaining it correctly. Alas I could not get the query to work as I filled in the missing bits and the syntax would just not work.

[BKBK]

tribule wrote:

I not only want the same dates, but I want to know if today's date is within the to and from date in the table. Does your query do that? Sorry but I don't understand if it does or not. If I have this data, from and to dates respectively:

2016-01-24  2016-01-28

2016-01-24  2016-01-25

2016-01-22  2016-01-24

Then if today's date is 2016-01-25 I would expect the first two rows to be returned, as today's date falls within the dates (inclusively) but the third row should not be returned, as the to date there has passed (24th was yesterday). Is this what your query does, as that is what I need? Sorry to be pedantic, I just want to make sure I'm explaining it correctly. Alas I could not get the query to work as I filled in the missing bits and the syntax would just not work.

Your explanation is now clearer. The suggestion that I gave you was based on what you said originally. You said you "need to build a query that returns a recordset for only those dates that fall within the current date.". That means something else of course.

Given this new explanation, I wonder why you used dateAdd and why you added 1 to dataFromDD. You could have just gone with

WHERE

   GETDATE() >= CAST(CAST(DateFromYYYY AS varchar) + '-' + CAST(DateFromMM AS varchar) + '-' + CAST(DateFromDD AS varchar)) AS DATETIME

AND

    GETDATE() <= CAST(CAST(DateToYYYY   AS varchar) + '-' + CAST(DateToMM   AS varchar) + '-' + CAST(DateToDD AS varchar)) AS DATETIME

[tribule]

BKBK, this is like what I had originally and it was always a day out or a day early, hence my dateAdd()'s. Your query gives me an 'incorrect syntax at CAST AS' error.

Eddie, your query also had a syntax error. I think I will use date fields in the database and change the application. Thanks again for all your help.

[EddieLotter]

tribule wrote:

I think I will use date fields in the database and change the application.

That's contrary to what you originally posted:

tribule wrote:

We can't change the fields in the database as this is a legacy system.

Good luck.

Cheers

Eddie

[tribule]

Never say never eh? I can write a script to change all the existing data to the new date-type format. It's not ideal, but best idea going forward I think as I've tried lots of suggestions and none work 100%.

[BKBK]

tribule wrote:

BKBK, this is like what I had originally and it was always a day out or a day early, hence my dateAdd()'s. Your query gives me an 'incorrect syntax at CAST AS' error.

I am surprised it now gives an error. The same statement had worked in your previous query, albeit giving the answer you didn't expect. In any case, here is another try:

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]

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.