Post reply

Date period from 00:00 to 23:59

  • July 23, 2011 at 6:06 am

    #240383

    Hi

    I have a script that returns a set of data that I need to send out via Reporting Services on a Tuesday.

    I need the data to be based on the previous week Monday to Sunday.

    I am using the following in my WHERE clause

    where ((inf.vw_IXP_PSNEW_ADMS_AND_DIS.admdate_dte >= (GETDATE () -8 )) and (inf.vw_IXP_PSNEW_ADMS_AND_DIS.admdate_dte <= (GETDATE () -1)))

    This only gives me the times from 12:00 noon Sunday to 12:00 noon Monday. I need to show the data that is from Sunday 00:00 to Monday 23:59.

    Does anbody have any ideas. Thanks in advance

  • July 23, 2011 at 6:37 am

    #1357414

    You can remove (actually set to midnight) the value of GETDATE by:

    SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),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]

  • July 23, 2011 at 8:08 am

    #1357419

    Many Thanks Ron

    That seems to have done the job.

    Regards Paul

  • July 23, 2011 at 1:48 pm

    #1357455

    CELKO (7/23/2011)

    >> I need the data to be based on the previous week Monday to Sunday. <<

    Create a report period table and join to it.

    CREATE TABLE ReportTimeslots

    (range_name CHAR(18) NOT NULL PRIMARY KEY,

    report_start_time DATETIME NOT NULL,

    report_end_time DATETIME NOT NULL,

    CHECK (report_start_time < report_end_time));

    Then do your query.

    SELECT R.range_name, COUNT(*) AS event_cnt

    FROM ReportTimeslots AS R

    LEFT OUTER JOIN

    Events AS E

    ON E.event_time BETWEEN R.report_start_time AND R.report_end_time

    GROUP BY R.range_name;

    What are you defining the start/end times in the table in the table as? "Closed/Closed", "Closed/Open", or "Open/Open? You just can't make a recommendation without that bit of information because it will determine how all queries against the date range of the table are written. 😉

    My personal recommendation would be to use "Closed/Open" where the StartDateTime is inclusive and the EndDateTime is the first non-inclusive date. That way you can avoid the horrible mistake of using BETWEEN for date ranges which frequently and mistakingly includes midnight of the next day or misses the last instants of the desired range because of the relatively poor granularity of even some of the new temporal datatypes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 23, 2011 at 2:22 pm

    #1357462

    Jeff Moden (7/23/2011)

    ....

    My personal recommendation would be to use "Closed/Open" where the StartDateTime is inclusive and the EndDateTime is the first non-inclusive date. That way you can avoid the horrible mistake of using BETWEEN for date ranges which frequently and mistakingly includes midnight of the next day or misses the last instants of the desired range because of the relatively poor granularity of even some of the new temporal datatypes.

    The advise not to use BETWEEN with datetime datatype with is a SQLServer to avoid its glitch is indeed the one to be emphasized. :exclamationmark:

    Adding the explanation deserves an extra point :w00t:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 25, 2011 at 2:50 am

    #1357645

    Hi Ron,

    Your query workd fine but gives me data from Monday 00:00 to Monday 00:00.

    Although it is only an extra minute I will pick up unwanted records.

    i only need to show Monday 00:00 to Sunday 23:59.

    Thanks in advance

    I'm using the following sheduled for a Tuedsay.

    where ((inf.vw_IXP_PSNEW_ADMS_AND_DIS.admdate_dte >= (DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) -9 ))

    and (inf.vw_IXP_PSNEW_ADMS_AND_DIS.admdate_dte <= (DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) -1 )))

  • July 25, 2011 at 3:25 am

    #1357650

    That can work with

    where ((inf.vw_IXP_PSNEW_ADMS_AND_DIS.admdate_dte >= (DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) -9 ))

    and (inf.vw_IXP_PSNEW_ADMS_AND_DIS.admdate_dte < (DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) -1 )))

    It will check for the time for >=00:00 and upto <00:00 which mean expect value up-to 23:59

    Regards,

    Mitesh Oswal

    Regards,
    Mitesh OSwal
    +918698619998

  • July 25, 2011 at 4:40 am

    #1357674

    Thanks, oversight on my behalf.

    Cheers

Viewing 8 posts - 1 through 7 (of 7 total)

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