kpax
asked on
SQL Command to get Hourly Dates
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
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
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have tried to do this same thing before and have not found a much more elegant answer than the sort solution46 suggested.
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>
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>
Opps solution46: Looks like we took the same track - only you submitted right before I did!
Heh...
Nice function... still like my sproc better though :)))))
s46
Nice function... still like my sproc better though :)))))
s46
cheers kpax!
s46
s46
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