Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    4

    Unanswered: Issue to get the list of dates

    I have a requirement to get the breakup of months and days within given date range for example if the FromDate ='20120223' and ToDate ='20120405'.
    We are using the version sybase ASE 11.9.2.6

    Need to insert these records to temp table like below based on the above from and two dates

    FromDate ToDate TotalDays
    '23/02/2012' '28/02/2012' 6
    '01/03/2012' '31/03/2012' 31
    '01/04/2012' '05/04/2012' 5

    -------------------------------------------------------------

    here is the sql iam using

    declare @from_Date datetime,
    @To_Date datetime,
    @month_diff int,
    @days int

    select @from_Date='20120223'
    select @To_Date ='20120405'

    begin
    select @days = datediff(dd,@from_Date, @To_Date)


    select @months_diff = datepart(mm,@To_Date) - datepart(mm, @from_Date)

    if @months_diff < 0
    select @months_diff = (@months_diff + 12)

    if @months_diff > 0
    begin
    if datepart(dd,@To_Date) >= datepart(dd,@from_Date)

    --Need to insert date to temp tables here


    end
    end



    Tried to find the month difference and then loop using while loop to insert to temp table.

    But not working out.

    Please help me out in this

    Many thanks

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,
    Pelase run the code in the attachment.

    Does what you want. Inserts on a temporary table called #temp.
    The code has comments so that you can see and understand the steps.
    Instead of using a cursor i used a while loop.


    Hope it helps!
    Attached Files Attached Files

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    If you have a numbers table then you don't need a loop
    Code:
    DECLARE @fromdt DATETIME, @todt datetime
    SELECT @fromdt='20120223', @todt='20130405'
    
    SELECT 
      FromDate, ToDate, TotalDays=datediff(dd,FromDate,ToDate)+1
    INTO #t1
    FROM 
      (SELECT 
          FromDate=CASE WHEN FromDate<@fromdt THEN @fromdt ELSE FromDate END 
         ,ToDate=CASE WHEN ToDate>@todt THEN @todt ELSE ToDate END 
       FROM 
         (SELECT
             FromDate=dateadd(mm,datediff(mm,'',@fromdt)+number,'')
            ,ToDate=dateadd(dd,-1,dateadd(mm,datediff(mm,'',@fromdt)+number+1,''))
          FROM 
            master..spt_values -- substitute for numbers table
          WHERE type='P' 
            AND number <=datediff(mm,@fromdt,@todt)
         )view1
      )view2
    ORDER BY FromDate
    With ASE 11 you would probably have to change it to
    Code:
    DECLARE @fromdt DATETIME, @todt datetime
    SELECT @fromdt='20120223', @todt='20130405'
    
    SELECT 
       FromDate=@fromdt
      ,ToDate=dateadd(dd,-1,dateadd(mm,datediff(mm,'',@fromdt)+1,''))
      ,TotalDays=datediff(dd, @fromdt, dateadd(dd,-1,dateadd(mm,datediff(mm,'',@fromdt)+1,'')) )+1
    INTO #t1
    UNION ALL 
    SELECT
       dateadd(mm,datediff(mm,'',@fromdt)+number,'')
      ,dateadd(dd,-1,dateadd(mm,datediff(mm,'',@fromdt)+number+1,''))
      , datediff(dd
         ,dateadd(mm,datediff(mm,'',@fromdt)+number,'')
         ,dateadd(dd,-1,dateadd(mm,datediff(mm,'',@fromdt)+number+1,'')) )+1
    FROM master..spt_values -- substitute numbers table
    WHERE type='P' 
      AND number BETWEEN 1 AND datediff(mm,@fromdt,@todt)-1
    UNION ALL   
    SELECT 
       dateadd(mm,datediff(mm,'',@fromdt)+ datediff(mm,@fromdt,@todt) ,'')
      ,@todt
      ,datediff(dd
        ,dateadd(mm,datediff(mm,'',@fromdt)+ datediff(mm,@fromdt,@todt) ,'')
        ,@todt)+1

Posting Permissions

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