Skip to main content
T
tribule
Brainiac
January 19, 2016
Question

Help with date query needed

  • January 19, 2016
  • 2 replies
  • 3009 views

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.
    This topic has been closed for replies.

    2 replies

    BKBK
    BKBK
    Adobe Expert
    January 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.

    T
    tribuleAuthor
    Brainiac
    January 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.

    BKBK
    BKBK
    Adobe Expert
    January 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

    EddieLotter
    EddieLotter
    Inspiring
    January 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

    T
    tribuleAuthor
    Brainiac
    January 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.

    EddieLotter
    EddieLotter
    Inspiring
    January 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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices