Need help to modify my SQL code

Question

I wanted to generate datetime ranges in SQL Server 2000. I want something that will be compatible with 2000, 2005 and 2008. If its compatible with 2012, that would be great too.

So, I found the answer here - Generate Dates between date ranges . But, I want two columns instead of 1. I want a start date column and an end date column. How do I modify the query below to get it ?

-- Declarations

DECLARE @dates TABLE(dt datetime)    
DECLARE @dateFrom datetime
DECLARE @dateTo datetime

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'

-- Query:

WHILE(@dateFrom < @dateTo)
BEGIN
   SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
   INSERT INTO @dates 
   SELECT @dateFrom
END

-- Output

SELECT * FROM @dates

My code -

DECLARE @dates TABLE(StartDate datetime, EndDate datetime)    
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'

WHILE(@dateFrom < @dateTo)
BEGIN
   SELECT @dateFrom, @dateFrom = DATEADD(day, 1,@dateFrom)
   INSERT INTO @dates 
   SELECT @dateFrom
END

SELECT * FROM @dates

Error - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

1) add the column to `@dates` 2) change your `INSERT` statement. Which part are you stuck on? — D Stanley, Nov 12 '13 at 21:40
it is far easier do this using a numbers table, and it will be loop free. — KM., Nov 12 '13 at 21:40
Can you provide some sample data to clarify what you mean by ... "I want two columns instead of 1" — RThomas, Nov 12 '13 at 21:40

Aaron Bertrand · Accepted Answer · 2013-11-12T22:23:59.710

5

Here's a set-based solution that uses spt_values, a system table found in all versions of SQL Server from 2000 -> 2014. In 2000, this will be limited to a date range of 256 days. In more modern versions, this upper limit will be 2048.

Typically a set-based solution is going to work better than any type of WHILE loop.

DECLARE @start DATETIME, @end DATETIME;

SELECT @start = '20010101', @end = '20010112';

-- INSERT @dates(StartDate, EndDate)
SELECT 
  StartDate = DATEADD(DAY, number,   @start), 
  EndDate   = DATEADD(DAY, number+1, @start)
FROM master..spt_values 
WHERE [type] = 'p' 
AND number < DATEDIFF(DAY, @start, @end);

That said, and particularly given the 256-day limitation, you should just consider a numbers and/or calendar table of your own to get around this limitation:

http://www.sqlperformance.com/generate-a-set-1

http://www.sqlperformance.com/generate-a-set-2

http://www.sqlperformance.com/generate-a-set-3

So you could create one using the following code (assuming that 100,000 days is a large enough range for you):

SELECT TOP 100000 Number = IDENTITY(INT,1,1) 
INTO dbo.Numbers 
FROM sysobjects AS s1 
CROSS JOIN sysobjects AS s2
ORDER BY 1;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number);

The above took all of 4.2 seconds on my 2000 system. With that table created, the query becomes even easier (and far less restrictive):

DECLARE @start DATETIME, @end DATETIME;

SELECT @start = '20010101', @end = '20010112';

-- INSERT @Dates(StartDate, EndDate)
SELECT 
  StartDate = DATEADD(DAY, Number-1, @start), 
  EndDate   = DATEADD(DAY, Number,   @start)
FROM dbo.Numbers 
WHERE Number <= DATEDIFF(DAY, @start, @end);

edited Nov 12 '13 at 22:23

answered Nov 12 '13 at 21:46

Aaron Bertrand

272,866
37
466
490

will there be any limitations to this method ? – Hydra Nov 12 '13 at 21:48
1

@Hydra yes, in 2000 (are you really still using 2000?) it will be limited to 256 days. Otherwise, I don't think so, maybe you could be more specific... – Aaron Bertrand Nov 12 '13 at 21:50
Aaron - I have no choice but to use 2000. – Hydra Nov 12 '13 at 21:52
@Hydra, I avoid using system tables in production code, their usage won't pass my code review. Just create your own true `Numbers` table and you'll be in control of it (including the range of numbers). My answer links to how to generate your own Numbers table. – KM. Nov 12 '13 at 22:11
@KM. I agree, and I mention and link to three posts about creating your own tables too. However, you don't always have control over creating new tables in all of the places where you might run this code. – Aaron Bertrand Nov 12 '13 at 22:12
Will JAS answer have any limitations such as 256 day limit in SQL server 2000 ? – Hydra Nov 12 '13 at 22:14
@AaronBertrand - What is the maximum range in SQL server 2005 ? – Hydra Nov 12 '13 at 22:25
@Hydra 2047 or 2048 depending on how you structure the query. – Aaron Bertrand Nov 12 '13 at 22:26
you could try something like `SELECT distinct a1.number+a2.number FROM master..spt_values a1 cross join master..spt_values a2 WHERE a1.[type] = 'p' and a2.[type] = 'p' order by 1` to get more values – KM. Nov 12 '13 at 22:29

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

this uses a numbers table:

DECLARE @dateFrom datetime, @dateTo datetime

SELECT @dateFrom = '2001/01/01'
      ,@dateTo = '2001/01/12'

select
    @dateFrom+Number-1
    From Numbers
    WHERE Number<=datediff(day,@dateFrom,@dateTo+1)

OUTPUT:

-----------------------
2001-01-01 00:00:00.000
2001-01-02 00:00:00.000
2001-01-03 00:00:00.000
2001-01-04 00:00:00.000
2001-01-05 00:00:00.000
2001-01-06 00:00:00.000
2001-01-07 00:00:00.000
2001-01-08 00:00:00.000
2001-01-09 00:00:00.000
2001-01-10 00:00:00.000
2001-01-11 00:00:00.000
2001-01-12 00:00:00.000

(12 row(s) affected)

I'm unsure what "2" columns the OP wants, but it is easy in the query:

select
    @dateFrom+Number-1,@dateFrom+Number
    From Numbers
    WHERE Number<=datediff(day,@dateFrom,@dateTo)

OUTPUT:

----------------------- -----------------------
2001-01-01 00:00:00.000 2001-01-02 00:00:00.000
2001-01-02 00:00:00.000 2001-01-03 00:00:00.000
2001-01-03 00:00:00.000 2001-01-04 00:00:00.000
2001-01-04 00:00:00.000 2001-01-05 00:00:00.000
2001-01-05 00:00:00.000 2001-01-06 00:00:00.000
2001-01-06 00:00:00.000 2001-01-07 00:00:00.000
2001-01-07 00:00:00.000 2001-01-08 00:00:00.000
2001-01-08 00:00:00.000 2001-01-09 00:00:00.000
2001-01-09 00:00:00.000 2001-01-10 00:00:00.000
2001-01-10 00:00:00.000 2001-01-11 00:00:00.000
2001-01-11 00:00:00.000 2001-01-12 00:00:00.000

if you need to create a numbers table, see: What is the best way to create and populate a numbers table?

edited May 23 '17 at 12:28

Community

1
1

answered Nov 12 '13 at 21:45

KM.

101,727
34
178
212

error - Msg 208, Level 16, State 1, Line 10 Invalid object name 'Numbers'. – Hydra Nov 12 '13 at 21:50
1

@Hydra you need to create and populate the numbers table. See the links in my answer. – Aaron Bertrand Nov 12 '13 at 21:51
@Hydra, see the link at the bottom of my answer. It will show you how to create a `Numbers` table. this will allow you to have a larger date range possible than using `master..spt_values` – KM. Nov 12 '13 at 21:54
@KM. - I prefer not to create a Numbers table. Its not my personal system/database. – Hydra Nov 12 '13 at 22:14
@Hydra, you are limited to the following: 1) create a Numbers table, 2) rely on a systems table that has a small sequence of numbers in it, 3) drop support for SQL Server 2000, 4) use a loop. I would poke my eye out before I used a loop for this. use a loop if you want, why you don't want to create a table? what is the big deal? it is up to you. – KM. Nov 12 '13 at 22:21
@KM. - DBA is not allowing me to create any SP, Table etc. I am a beginner. That is why. – Hydra Nov 12 '13 at 22:24
@Hydra so ask your DBA to create a numbers table for you. If he doesn't understand how it can help solve this problem, I'm not sure you should call him a DBA. – Aaron Bertrand Nov 12 '13 at 22:25
@Hydra, ask the DBA if they want you writing a loop or using a numbers table. – KM. Nov 12 '13 at 22:26

score -4 · Answer 3 · answered Nov 12 '13 at 21:44

-4

-- Declarations

DECLARE @dates TABLE(dt datetime, dt2 datetime)    
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
DECLARE @endDate datetime

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'

-- Query:

WHILE(@dateFrom < @dateTo)
BEGIN
   SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
   SELECT @endDate = DATEADD(day, 1,@dateFrom)
   INSERT INTO @dates 
   SELECT @dateFrom, @endDate
END

-- Output

SELECT * FROM @dates

answered Nov 12 '13 at 21:44

Jas

888
1
8
12

1

a loop is unnecessary in such a trivial query. – KM. Nov 12 '13 at 21:58
@KM. - Will this have any limitations such as 256 day limit in SQL server 2000 ? – Hydra Nov 12 '13 at 22:14
1

@Hydra, I would **never** use this looping code, **never ever ever ever.** – KM. Nov 12 '13 at 22:15
@KM. - why would you say that ? It looks fine to me., – Hydra Nov 12 '13 at 22:16
@Hydra loops are inefficient and unnecessarily bulky code to boot. Have you bothered to read any of the links that have been presented to you today? – Aaron Bertrand Nov 12 '13 at 22:18
1

KM is simply saying that looping here is unnecessary and inefficient, which is true. But it does answer the asked question with no limitations or extra tables needing to be created. – Jas Nov 12 '13 at 22:19
@AaronBertrand - I am a beginner. All those links are too much information for me. Is there a summary of what the author is trying to prove ? – Hydra Nov 12 '13 at 22:22
2

@Hydra That comment pisses me off to no end. Too much information! How about you read them anyway absorb that information into your brain and use it for future solutions so you don't have to ask SO every time you hit a problem. You're a beginner great then that's the perfect chance to start learning. – Zane Nov 12 '13 at 22:25
@Hydra yes, and it is the second paragraph in my answer: a set-based solution is going to work better than any type of WHILE loop. If you don't want to take our word for it, you're going to have to read, sorry. Can't always have your cake and eat it too. – Aaron Bertrand Nov 12 '13 at 22:25
@Hydra Try w3schools.com – Kermit Nov 12 '13 at 22:26
If the days to be added are greater than the difference (in days) between start date and end date, then I get wrong start date and enddate. Note - dates are in yyyy-mm-dd format example - if dateFrom = '2001/01/01', dateTo = '2001/01/02', and days = 7, then, StartDate should be 2001/01/01 and EndDate should be 2001/01/02. But I am getting 2001-01-08 00:00:00.000 and 2001-01-15 00:00:00.000, which is wrong. How do i fix my code ? Also, is there a way to prevent negative numbers from being passed into @days ? – Hydra Nov 12 '13 at 23:18

Need help to modify my SQL code

3 Answers3