Post reply

How to select on the previous days date

  • November 10, 2009 at 4:08 pm

    #227820

    HI,

    I have a table that stores data relating to service request - id, date, type etc. I want to create a SRSS Report.

    I would like to extract all the data where the date entered is the previous date as of the current date when the report would be run. So if i run my report anytime on a Monday (for example) then it would retun all the service requests entered the day before (in this case Sunday)

    Any help wpould be much appreciated

    Thanks

    Greg

  • November 10, 2009 at 4:41 pm

    #1077590

    Greg Albrecht-252263

    Greg it would help those who want to help you if you would provide further information. Is your date a DATETIME value, or is it month/day/year in a VARCHAR column? What about holidays, can we assume since you collect data on a Sunday, that holidays are also working days?

    Please refer to my signature block on how to post to help those who want to help you.

    With all that said try the link below to learn more than you want to know about date/time functions and their manipulation. I believe you will find the answer to your question.

    Semingly endless T_SQL for various date time calculations

    https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

    in particular this code:

    Beginning of previous day

    select dateadd(wk, datediff(wk, 0, @ThisDate), 0)

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 10, 2009 at 4:41 pm

    #1077591

    You need to look up DATEADD in help (Books Online). You are going to setup a query where you will filter the results using the above function. Example:

    SELECT {columns}

    FROM table

    WHERE YourDateColumn >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0) -- yesterday

    AND YourDateColumn < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) -- today

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • November 11, 2009 at 2:09 pm

    #1078034

    Thanks for the reply and solution. It worked for me - Much appreciated

    Regards

    Greg

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply