SQL Date Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Date Query

Author

Topic

Stanley Tan
Starting Member

25 Posts

Posted - 2003-06-27 : 07:03:36

Hi,

Is it possible to create a SQL statement that will return all date values from a given start and end point? For instance:

Start: 05/01/03
End: 05/05/03

Result:
05/01/03 some data
05/02/03 some other data
05/03/03
05/04/03 some data
05/05/03 blah blah

This query:

SELECT MyDate, SUM(Amount) FROM MyTable
GROUP BY MyDate

does not generate the desired output if some dates are not present in the table such as 05/03/03 (should display blank data field).

Any help is greatly appreciated. :)

dsdeming

479 Posts

Posted - 2003-06-27 : 08:50:10

Yes. You can use a number table ( sometimes called a sequence or tally table ). There are several threads on the subjects in the forums. I use a table called Sequence with a single integer column called Counter that holds values from 1 - 8000. Basically it works like this:

DECLARE @dtFirst datetime, @TotalDays int

-- the CONVERTS on the datetime column are only required if you
-- need to strip the time
SELECT @dtFirst = MIN( CONVERT( datetime, CONVERT( char( 8 ), YourDateColumn, 112 ) )),
@TotalDays = DATEDIFF( dd, MIN( CONVERT( datetime, CONVERT( char( 8 ), YourDateColumn, 112 ) )),
MAX( CONVERT( datetime, CONVERT( char( 8 ), YourDateColumn, 112 ) )))
FROM YourTable

SELECT AllDays = DATEADD( dd, Counter - 1, @dtFirst )
FROM Sequence
WHERE Counter <= @TotalDays + 1

The last SELECT statement could easily be joined in a more complex select.

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources