I need a SQL statement that will output Hourly dates from 2 date inputs.
Example:
Inputs: 9/16/2004 12:00:00 AM startdate
9/17/2004 11:59:59 PM enddate
9/16/2004 12:00:00 AM
9/16/2004 01:00:00 AM
9/16/2004 02:00:00 AM
9/16/2004 03:00:00 AM
....
9/17/2004 09:00:00 PM
9/17/2004 10:00:00 PM
9/17/2004 11:00:00 PM
Wold this be possible in sql and can I use a for loop in sql?
Thanks
kpax
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
If you need to find the difference between two dates then use the DateDiff command in sql. That will allow you to compare two dates and give you the difference.
ie.
SELECT * FROM table WHERE DateDiff("n", DateTimeField, Now()) > 15;
Will result in records that have a time older than 15 minutes. Lookup the DateDiff syntax for more information on the switches you can use to produce different time, such as months, days, minutes, hours, etc.
Section 37
Thanks for your reply.
Basically what I want to be able to di is create a dataset in my .Net app that has all
datetimes per hour from a start date and an end date.
From this dataset I can be able to get the hour using the datepart function in crystal report which will allow me to create a report showing totals for each hour(also showing data for the hour that has none) - this part I can take care of.
But the only difficult part is getting date times per hour between 2 dates in sql.
Thanks
kpax
Hmm.. I think he means that he needs to genberate all the distinct hours between two passed dates...
Try this (it takes the first time, adds one hour to it and inserts the value into a new table, repeats until it reaches the second time).
Regards,
s46.
CREATE PROC spHourOutput
@TimeFrom datetime,
@TimeTo datetime
AS
DECLARE @TimeTemp datetime
SET @TimeTemp = @TimeFrom
WHILE @TimeTemp <= @TimeTo
BEGIN
INSERT INTO your_table(your_field)
VALUES (@TimeTemp)
SET @TimeTemp = DateAdd(hh, 1, @TimeTemp)
END
GO
I think this is what you wanted:
Create a custom function:
CREATE FUNCTION DumpHours (@startDate datetime, @endDate datetime)
RETURNS @resultTable TABLE
(result1 varchar(80) )
AS
Begin
declare @counter datetime
set @counter = @startDate
while @counter < @endDate
begin
insert @resultTable select @counter
set @counter = dateAdd (Hour,1,@counter)
end
return
END
and use it in a SQL query:
select * from dumpHours('9/16/2004 12:00:00 AM ','9/17/2004 11:59:59 PM')
Tiran Dagan
<advertizing removed by CetusMOD per http:help.jsp#hi106>
Business Accounts
Answer for Membership
by: Section37Posted on 2004-09-17 at 10:18:12ID: 12086595
My best thought for you is to setup a DTS package and then schedule it on the hour with the Server Agent.
I am not too sure what you mean though, but if you needed the information inserted, then all you would have to do is setup a DTS package the executes the SQL Insert statment that you want. Then with the Server Agent you can schedule it to run hourly every day.
Hope that helps!
Section37