[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Command to get Hourly Dates

Posted on 2004-09-17
9
Medium Priority
?
2,218 Views
Last Modified: 2008-01-09
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
         
0
Comment
Question by:kpax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 3

Expert Comment

by:Section37
ID: 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
0
 
LVL 3

Expert Comment

by:Section37
ID: 12086624
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
0
 

Author Comment

by:kpax
ID: 12086768
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 9

Accepted Solution

by:
solution46 earned 1000 total points
ID: 12086807
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
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12086869
I have tried to do this same thing before and have not found a much more elegant answer than the sort solution46 suggested.
0
 
LVL 3

Expert Comment

by:tirandagan
ID: 12086908
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>
0
 
LVL 3

Expert Comment

by:tirandagan
ID: 12086929
Opps solution46: Looks like we took the same track - only you submitted right before I did!
0
 
LVL 9

Expert Comment

by:solution46
ID: 12086963
Heh...

Nice function... still like my sproc better though :)))))


s46
0
 
LVL 9

Expert Comment

by:solution46
ID: 12088382
cheers kpax!

s46
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question