How to get data betweeen an hour

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get data betweeen an hour

Author

Topic

maksuda
Starting Member

15 Posts

Posted - 2010-01-11 : 16:37:57

Hi:
I have a production table where I have huge data and need to execute processes (stored proc) every hour after pooling data(my database pools data every hour). In my query I want to select only one hour data to execute all prosses. So I need to create my process like:
select a,b,c from T1 where dt between dt1 and dt2.
Format of dt1 and dt2 should be yyyy-mm-dd hr:00:00 and
yyyy-mm-dd hr:59:59
what I mean is when my process will run it will select yyyy,mm,dd,hh from system and then I subtract 1 from hh (hh1=like dateadd(hh,-1,dt1)).
Then I want to make dt1 = yyyy-mm-dd-hh1:00:00 and
dt2= yyyy-mm-dd-hh1:59:59

How can I do that...please need your help

Thnks in advance
Maksuda

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 16:44:40

[code]select a,b,c from T1 where dt > dateadd(hour,-1,getdate())[/code]

maksuda
Starting Member

15 Posts

Posted - 2010-01-11 : 17:19:02

Thanks vijaysonly for the reply. Unfortunately I cant do that, because if I do dateadd(hour,-1,getdate()) then system will pick the current Date and Time (hh with min:sec) and will subtract 1 from that datetime......which I don't want..........
If I do that like if I my process executes at 10:20:00 PM then dateadd(hour,-1,getdate()) will give me (yyyy,mm,dd 21:20:00)
If process executes at 1:20 AM (which means next date) then dateadd(hour,-1,getdate()) will give (yyyy,mm,dd 00:20:00) which means my select query will select some data from previous date and some data from next date - it will effect my monthly process.
So I need to select exactly one hour data form(yyyy,mm,dd hh:00:00) to (yyyy,mm,dd hh:59:59)
like from (2010-01-11 23:00:00) to (2010-01-11 23:59:59) which will give me the perfect selection
I don't know if I could explain my problem or not...........

Thanking you,
Maksuda

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-11 : 17:54:15

It is not easy to understand.
Maybe some sample data and wanted output will give us the right direction...

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 19:27:10

quote:
Originally posted by maksuda

Thanks vijaysonly for the reply. Unfortunately I cant do that, because if I do dateadd(hour,-1,getdate()) then system will pick the current Date and Time (hh with min:sec) and will subtract 1 from that datetime......which I don't want..........
If I do that like if I my process executes at 10:20:00 PM then dateadd(hour,-1,getdate()) will give me (yyyy,mm,dd 21:20:00)
If process executes at 1:20 AM (which means next date) then dateadd(hour,-1,getdate()) will give (yyyy,mm,dd 00:20:00) which means my select query will select some data from previous date and some data from next date - it will effect my monthly process.
So I need to select exactly one hour data form(yyyy,mm,dd hh:00:00) to (yyyy,mm,dd hh:59:59)
like from (2010-01-11 23:00:00) to (2010-01-11 23:59:59) which will give me the perfect selection
I don't know if I could explain my problem or not...........

Thanking you,
Maksuda

You can have a look at this...
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

You might make use of dbo.Time(datepart(hh,getdate()),0,0) and dbo.Time(datepart(hh,getdate()),59,59) and ad it to your current date as explained.

maksuda
Starting Member

15 Posts

Posted - 2010-01-12 : 11:00:55

Sorry webfred for making problem not easy understanding. Here I describe the whole thing.............

I have a process which inports data from remote db to my db every hour. Process runs evey hour like 2010-01-11 12:10 AM,
2010-01-11 1:10 AM,2010-01-11 2:10 AM, 2010-01-11 3:10 AM,...2010-01-11 10:10PM,2010-01-11 11:10 PM. Every hour it imports one hour's data like at 2010-01-11 12:10 AM it imports data from 2010-01-10 11:00:00 to 2010-01-10 11:59:59, at 2010-01-11 1:10 AM it imports data from 2010-01-11 00:00:00 to 2010-01-11 00:59:59...., at 10:10PM imports data from 22:00:00 to 22:59:59 ,at 11:10 PM imports from 23:00:00 to 23:59:59.....it's an on going process.
What I have to do, after importing data have to execute daily,monthly,qtrly and yearly processes to generate the reports (actually I have created SQL jobs and scheduled them). So that management gets very update information.

My database is huge, so if I select current month's data for the monthly report, current qtr's data for the Qtrly report and current year's data for the yearly report(what I'm doing now)........... it takes long time......... and all my SQL jobs take more than an hour to finsh......which impacts other application......
For executing all processes I need to select exactly one hour's data that have been just imported.
Selecting only one hour's data in my processes takes very very less time.
For my processes I want to select each hour's data like when my process executes at 2010-01-11 12:10 AM I want to select data from 2010-01-10 11:00:00 to 2010-01-10 11:59:59, at 2010-01-11 1:10 AM select data from 2010-01-11 00:00:00 to
2010-01-11 00:59:59......at 10:10PM select data from 22:00:00 to 22:59:59 ,at 11:10 PM select from 23:00:00 to 23:59:59..... and so on.

How can I make my two date fields (dt1 and dt2) like 2010-01-10 11:00:00(from date) and 2010-01-10 11:59:59(to date) when my process will execute at 2010-01-11 12:10 AM , then at 2010-01-11 1:10 AM 2010-01-10 00:00:00(from date) and 2010-01-10 00:59:59(to date)

Any kind of help is highly appreciated.

Thanking You,
Maksuda

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-12 : 11:41:36

Try this

select a,b,c 
from T1 
where dt between 
dateadd(d, datediff(d, 0, getdate()), 0) + dateadd(ss,(datepart(hh,getdate())*3600)+(0*60)+0,0)
and 
dateadd(d, datediff(d, 0, getdate()), 0) + dateadd(ss,(datepart(hh,getdate())*3600)+(59*60)+59,0)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-12 : 11:46:28

If you want the previous hour...subtract 1 from the datepart result

select a,b,c 
from T1 
where dt between 
dateadd(d, datediff(d, 0, getdate()), 0) + dateadd(ss,((datepart(hh,getdate())-1)*3600)+(0*60)+0,0)
and 
dateadd(d, datediff(d, 0, getdate()), 0) + dateadd(ss,((datepart(hh,getdate())-1)*3600)+(59*60)+59,0)

maksuda
Starting Member

15 Posts

Posted - 2010-01-12 : 12:01:26

Thanks vijayisonly for the help..........it's exactly the code that I was looking for.
Thank you very much.........keep it up...........

Thanking you,
Maksuda....

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-12 : 12:06:14

Np. You're welcome.

Like I said, you will just have to modify the parameters from the function that was provided in the link. So, have a look at that link too.

maksuda
Starting Member

15 Posts

Posted - 2010-01-12 : 16:11:00

I read the link. Several date and time functions are there.....it's a good link......thanks for sending such a good link.

Regards,
Maksuda

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources