Help with date query needed

Report · Jan 19, 2016

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.

Report · Jan 19, 2016

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

Report · Jan 20, 2016

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.

Report · Jan 20, 2016

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

Report · Jan 21, 2016

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

Report · Jan 21, 2016

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

Report · Jan 21, 2016

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?

Report · Jan 21, 2016

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

Report · Jan 22, 2016

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?

Report · Jan 25, 2016

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

Report · Jan 25, 2016

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.

Report · Jan 25, 2016

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

Report · Jan 25, 2016

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

Report · Jan 25, 2016

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

Report · Feb 01, 2016

Have you tried:

select * from mydatabase where

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

Report · Jan 24, 2016

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.

Report · Jan 25, 2016

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.

Report · Jan 25, 2016

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

Report · Jan 25, 2016

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.

Report · Jan 25, 2016

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

Report · Jan 26, 2016

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.

Report · Jan 26, 2016

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

Report · Jan 26, 2016

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%.

Report · Jan 26, 2016

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

Report · Jan 26, 2016

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.

Adobe Community

Help with date query needed