Help with date query needed

Enthusiast ,
Jan 19, 2016 Jan 19, 2016

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.

Views

1.1K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 19, 2016 Jan 19, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 20, 2016 Jan 20, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 20, 2016 Jan 20, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 21, 2016 Jan 21, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 21, 2016 Jan 21, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 21, 2016 Jan 21, 2016

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 21, 2016 Jan 21, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 22, 2016 Jan 22, 2016

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 25, 2016 Jan 25, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 25, 2016 Jan 25, 2016

Copy link to clipboard

Copied

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-212016-2-26
2016-1-12016-2-14
2015-1-12016-6-21
2015-1-12016-12-31
2015-1-12016-1-21
2015-1-12016-12-31

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 25, 2016 Jan 25, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 25, 2016 Jan 25, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 25, 2016 Jan 25, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 01, 2016 Feb 01, 2016

Copy link to clipboard

Copied

LATEST

Have you tried:

select * from mydatabase where

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jan 24, 2016 Jan 24, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 25, 2016 Jan 25, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jan 25, 2016 Jan 25, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 25, 2016 Jan 25, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jan 25, 2016 Jan 25, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 26, 2016 Jan 26, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 26, 2016 Jan 26, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 26, 2016 Jan 26, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jan 26, 2016 Jan 26, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 26, 2016 Jan 26, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines