TSQL Query - Returns all seconds between two dates
I'm shooting from the hip here, but here's the start:
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
SET @STARTDATE = '05/01/2010'
SET @ENDDATE = GETDATE()
-- Find the seconds between these two dates
SELECT DATEADD(SECOND, Number, @StartDate) AS N
FROM Numbers
WHERE Number < DATEDIFF(SECOND, @STARTDATE, @ENDDATE)
This assumes a table named Numbers with a column named Number containing values from 1 to. Being able to get results in a full month will need values up to 2.5 million. I would keep the query up to a day, meaning the Numbers table could go away with values less than 100,000.
Here is a great article on number tables: http://www.sqlservercentral.com/articles/Advanced+Querying/2547/
Registration is required, but it's free. If you are serious about SQL Server programming, this site is quite useful.
a source to share
To do this, you will most likely need a table of subsidiary numbers. Do you want all the seconds presented, or can you round to the nearest second and group?
Also how many seconds are we talking here and in what format you currently store them. Are they already rounded?
If not, then perhaps to avoid the overhead of rounding them or doing a query like BETWEEN every time (and also repeated DATEADDs), perhaps you could use Marc's DATEDIFF answer on insert / update time to store seconds from some base date then just join numbers table using calculated numeric column.
Code to create Numbers table from here http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html Just add Brad's answer.
CREATE TABLE dbo.Numbers
(
Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1000000
BEGIN
INSERT dbo.Numbers DEFAULT VALUES
END
a source to share