Results 1 to 3 of 3

Thread: Date headache

  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Question Unanswered: Date headache

    Guys
    I have a table 1 row, a start and end date of a period

    create table xperiod(startdate datetime , enddate datetime)
    insert xperiod (startdate , enddate)
    values ('2004-04-01 00:00:00.000' , 2012-03-31 00:00:00.000)


    I'm trying to retrieve a batch of 'smaller' periods from this where the relevant period is a number (of months) passed as a parameter (only ever 1, 3 or 6)


    for example, if the parameter is 1 I will obtain the following rows each being a 1 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value
    startperiod endperiod
    '2004-04-01 00:00:00.000' '2004-04-30 00:00:00.000'
    '2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'
    '2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'

    and so on to
    '2012-03-01 00:00:00.000' '2012-03-31 00:00:00.000'


    if the parameter is 3 I will obtain the following rows each being a 3 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value


    startperiod endperiod
    '2004-04-01 00:00:00.000' '2004-06-30 00:00:00.000'
    '2004-07-01 00:00:00.000' '2004-09-30 00:00:00.000'
    '2004-10-01 00:00:00.000' '2004-12-31 00:00:00.000'

    and so on to
    '2012-01-01 00:00:00.000' '2012-03-31 00:00:00.000'

    Hope this makes sense !

    I think I'll be ok on the logic for the while loop but my main problem is getting the endperiod value based on the startperiodvalue
    Thx in advance

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Code:
    --eg:for one month period 
    select dateadd(dd,-1,dateadd(mm,1,getdate())) as endperiod
    --eg:for 3 month period 
    select dateadd(dd,-1,dateadd(mm,3,getdate())) as endperiod
    --eg:for 6 month period 
    select dateadd(dd,-1,dateadd(mm,6,getdate())) as endperiod
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    37
    That's perfect - thanks

Posting Permissions

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