Generate datetime range SQL Server 2000

Question

I am trying to generate dates for the whole year of 2012 in datetime format to store in a SQL Server table in a datetime column.

How is that done?

So start date would be 1/1/2012 until 31/12/2012 but in the correct format. I am assuming I should have 365 rows generated. Any idea?

There has to be a more efficient way to do what you are trying to do. — Erik, Jan 29 '12 at 11:10
Actually no - having number and date tables is good practice in many areas as it allows full index usage on those. Plus you can provide calcualted fields on a Date table. Like "day of month" "days till end of month" etc. This allow efficient query by business level parameters without calculating them all the time. This is OLAP for beginners knowledge. Try doing a data warehouse for a change. — TomTom, Jan 29 '12 at 11:33
@Erik believe me, best option in relation to my requirement. The answer below is unclear. Am sure there is a procedure to generate such a range simply? — sys_debug, Jan 29 '12 at 11:41
You asked for idea, not for clear answer. I gave you the answer which do works. If you not understand what I do in **such a simple** query - try to learn more, may be even from this answer — Oleg Dok, Jan 29 '12 at 11:47
@TomTom not really interested in starting a discussion. As my dad would say: Should think beyond the length of your nose. — Erik, Jan 29 '12 at 12:04
@Erik I do - you should really read a book on large database design. Or try to work in this area. PHP and MySql are not really showing you know about those stuff ;) — TomTom, Jan 29 '12 at 12:25
Almost off topic, but don't rely on the number 365 - there are 366 days in 2012. — BlackWasp, Jan 29 '12 at 12:34

Oleg Dok · Answer 1 · 2012-01-29T11:49:44.343

3

CREATE TABLE DateTimeRange2012([Date] DATETIME NOT NULL)
GO
INSERT INTO DateTimeRange2012([Date])

SELECT DATEADD(day, Number, '20120101')
FROM master..spt_values
WHERE Number < 366 AND type = 'P'

Much more clear for keeping the values persisted?

edited Jan 29 '12 at 11:49

answered Jan 29 '12 at 11:31

Oleg Dok

21,109
4
45
54

hmmm am not sure what you are doing here? – sys_debug Jan 29 '12 at 11:41
hmmm well because I need an insert too of the whole range, a date per row, so I am assuming the above won't really achieve that :) – sys_debug Jan 29 '12 at 11:46

score 2 · Accepted Answer · answered Jan 29 '12 at 12:51

Nice answer from Oleg Dok.

To expand upon his answer, here's a stored procedure that accepts a year and generates all of the dates for the year. This takes into account leap years.

CREATE PROCEDURE GenerateDates (@Year INT)
AS

SET NOCOUNT ON

DECLARE @FirstDate DATETIME
DECLARE @NoOfDates INT

SET @FirstDate = DateAdd(Year, @Year-1900, 0)
IF MONTH(DATEADD(Day, 59, @FirstDate))=3
    SET @NoOfDates = 365
ELSE
    SET @NoOfDates = 366

INSERT INTO DateTimeRange([Date])
    SELECT DATEADD(day, Number, @FirstDate) 
    FROM master..spt_values 
    WHERE Number < @NoOfDates AND type = 'P'

One proviso. I believe that "spt_values" may not be supported and may be removed in future versions of SQL Server. However, I have read that it is being used in SQL Server 11 example code so perhaps it's not a worry just yet.

The `@NoOfDates` variable and the corresponding `IF` statement are not really needed. Just replace `@NoOfDates` in the `WHERE` clause with `DATEDIFF(DAY, @FirstDate, DATEADD(YEAR, 1, @FirstDate))`. — Andriy M, Jan 29 '12 at 15:06
Good point. I think I'd keep @NoOfDates for readability but the DATEDIFF is probably better than the IF. — BlackWasp, Jan 29 '12 at 21:06

Generate datetime range SQL Server 2000

2 Answers2