Post reply

query to retrieve only 5 hours data for current day

  • March 21, 2011 at 5:25 am

    #237128

    hi..., everyone! i am in need of a sql query to retrieve data from database for only 5 hours for the current day. I mean to say that if today is 21-03-2011 then i need to retrieve data for today for time 5:00:00 to 10:00:00. For next day also i need to fetch the data for the same time i.e(5:00:00 to 10:00:00).

    I have a database table having a column of DataTime with data type datetime. For every day i need to fetch the data for 5:00:00 to 10:00:00. i do not know whether i could able to explain my problem properly or not.

    please give me some suggestion so that i could get recovered from my problem.

    Thank you in advance.

  • March 21, 2011 at 5:44 am

    #1301202

    try to avoid fields having the same name as their datatype; use a name that is more descriptof the purpose... like "entryDate" or whatever the field might mean to you.

    when you fiddle with datetime fields, you'll need to get familiar with the DATEADD and DATEDIFF functions; there's a lot of smart techniques that goes with their usage.

    in the example below,the idea is to take the starting date of SQL server, and add as many whole days to it as exists in your datetime field....

    that technique gets you midnight of whatever date you were looking at.

    from there, you add the number of hours you want to build the "right" datetime:

    select

    -- gets midnight of the starting day of the date in question.

    DATEADD(dd, DATEDIFF(dd,0,getdate()), 0),

    --same as above: the date tot eh left and zero are the starting date for SQL server.

    DATEADD(dd, DATEDIFF(dd,0,getdate()), '1900-01-01 00:00:00.000'),

    --add five hours to get the desired starting time: is that 5am or 5pm? 5 hours or 17 hours to add?

    DATEADD(hh,5,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) ),

    --add ten hours to get the desired starting time: is that 10am or 10pm? 10 hours or 20 hours to add?

    DATEADD(hh,20,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) )

    --so the final WHERE statemetn woudl be something like

    WHERE [YourDateField] BETWEEN DATEADD(hh,5,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) )

    AND DATEADD(hh,20,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 21, 2011 at 5:53 am

    #1301207

    Thank you Sir. now i can proceed further. Thank you for your immense help....

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

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