Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    15

    Unanswered: Generate date sequence in a query

    Hi all!

    Please help me with one question.

    Is it possible to generate a sequence of dates in a Sybase query.
    The result of a query should look like:

    Code:
    Date
    --------
    01-07-2012 - min date
    02-07-2012
    03-07-2012
    ...
    01-01-2199 - max date
    It's easy to generate a sequence between min and max date in Oracle, but I didn't find any solution for Sybase...

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Use a numbers table
    Code:
    SELECT dateadd(dd,a.number*1025+b.number,'20120701')
    FROM master..spt_values a
       , master..spt_values b
    WHERE a.type='P'
      AND b.type='P'
      AND a.number<=1024
      AND b.number<=1024
      AND dateadd(dd,a.number*1025+b.number,'20120701')<='21990101'

  3. #3
    Join Date
    Jul 2012
    Posts
    15
    Oh! Thank you!
    Also I found another solution by myself.
    I created the followintg stored procedure:
    Code:
    CREATE PROCEDURE dbo.DateGenerator
    @StartDate date,
    @EndDate date
    AS
    Begin
    		Create table #tmpDate (Date date)
    		
    		Insert Into #tmpDate (Date) values (@StartDate)
    		
    		While @StartDate < @EndDate
    		begin
    				Select @StartDate = dateadd(dd, 1, @StartDate)
    				Insert Into #tmpDate (Date) values (@StartDate)
    		end
    		
    		Select Date From #tmpDate
    		
    		Drop table #tmpDate
    End
    After that I created the followint proxy table:
    Code:
    create existing table DateRange 
       (Date date null, _StartDate date null, _EndDate date null) 
    external procedure at 'loopback.<database name goes here>..DateGenerator'
    So now I can get a date range between a and b with select statement:
    Code:
    Select Date From DateRange Where _StartDate = a and _EndDate = b
    But your solution seems to work faster.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •