Get 10 months forward with select statment


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Get 10 months forward with select statment

  1. #1
    Nirit Touboul Guest

    Get 10 months forward with select statment


    Hi,

    Can I get 10 months forward with select statment without using the database
    table.

    Tahnk you,

    Nirit

  2. #2
    D. Patrick Hoerter Guest

    Re: Get 10 months forward with select statment

    Look at DATEADD

    Regards,
    D. Patrick Hoerter

    Nirit Touboul <nirit@para-data.com> wrote in message
    news:3a8f84c0$1@news.devx.com...
    >
    > Hi,
    >
    > Can I get 10 months forward with select statment without using the

    database
    > table.
    >
    > Tahnk you,
    >
    > Nirit




  3. #3
    Nirit Touboul Guest

    Re: Get 10 months forward with select statment



    Thank you for your answer but I mean to get a Recordset list of the months
    from the current month to 10 month forward

    Thank again,

    Nirit

    "D. Patrick Hoerter" <phoerter@NO_SPAMMERS_bellatlantic.NET_net> wrote:
    >Look at DATEADD
    >
    >Regards,
    >D. Patrick Hoerter
    >
    >Nirit Touboul <nirit@para-data.com> wrote in message
    >news:3a8f84c0$1@news.devx.com...
    >>
    >> Hi,
    >>
    >> Can I get 10 months forward with select statment without using the

    >database
    >> table.
    >>
    >> Tahnk you,
    >>
    >> Nirit

    >
    >



  4. #4
    James T. Stanley Guest

    Re: Get 10 months forward with select statment

    You could:
    1) Create a temp table with a column to hold month names
    2) Use GetDate() to put the current date in a local variable
    3) Use another variable as a counter from 1 to 10 for a While loop
    4) Inside the loop, perform an insert into the temp table using DateAdd and
    the current date, along with the loop variable to specify how many months to
    add
    5) Select * from the temp table


    "Nirit Touboul" <nirit@para-data.com> wrote in message
    news:3a90c2b2$1@news.devx.com...
    >
    >
    > Thank you for your answer but I mean to get a Recordset list of the months
    > from the current month to 10 month forward
    >
    > Thank again,
    >
    > Nirit
    >
    > "D. Patrick Hoerter" <phoerter@NO_SPAMMERS_bellatlantic.NET_net> wrote:
    > >Look at DATEADD
    > >
    > >Regards,
    > >D. Patrick Hoerter
    > >
    > >Nirit Touboul <nirit@para-data.com> wrote in message
    > >news:3a8f84c0$1@news.devx.com...
    > >>
    > >> Hi,
    > >>
    > >> Can I get 10 months forward with select statment without using the

    > >database
    > >> table.
    > >>
    > >> Tahnk you,
    > >>
    > >> Nirit

    > >
    > >

    >




  5. #5
    .. Guest

    Re: Get 10 months forward with select statment

    Hi Nirit,

    I'm not sure if this is what you meant, and it's not elegant, but you could
    do something similar to the following:

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

    declare @theMonth as integer
    set @theMonth = datepart(mm, getdate())

    if @theMonth = 1
    select
    'January', 1
    union
    select
    'February', 2
    union
    select
    'March', 3
    union
    select
    'April', 4
    union
    select
    'May', 5
    union
    select
    'June', 6
    union
    select
    'July', 7
    union
    select
    'August', 8
    union
    select
    'September', 9
    union
    select
    'October', 10
    union
    select
    'November', 11
    order by 2

    if @theMonth = 2
    select
    'February', 2
    union
    select
    'March', 3
    union
    select
    'April', 4
    union
    select
    'May', 5
    union
    select
    'June', 6
    union
    select
    'July', 7
    union
    select
    'August', 8
    union
    select
    'September', 9
    union
    select
    'October', 10
    union
    select
    'November', 11
    union
    select
    'December', 12
    order by 2

    if @theMonth = 3
    select
    'March', 3
    union
    select
    'April', 4
    union
    select
    'May', 5
    union
    select
    'June', 6
    union
    select
    'July', 7
    union
    select
    'August', 8
    union
    select
    'September', 9
    union
    select
    'October', 10
    union
    select
    'November', 11
    union
    select
    'December', 12
    union
    select
    'January', 13
    order by 2

    .....

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


    > [...] I mean to get a Recordset list of the months
    > from the current month to 10 month forward


    >> Can I get 10 months forward with select statment without using the
    >>database table.





  6. #6
    rLyra Guest

    Re: Get 10 months forward with select statment


    "Nirit Touboul" <nirit@para-data.com> wrote:
    >
    >
    >Thank you for your answer but I mean to get a Recordset list of the months
    >from the current month to 10 month forward
    >

    I don`t have a SQL help Reference here, so I don`t remember the sintax of
    this commands, you need to find it.

    1 - you need a "list of the months". This will be a list of year and month,
    ok ? So you need a function to extract year and month from a date.

    2 - You also need that dateadd funcion sintax, because I don`t remember.


    3 - To "the current month"you will use TODAY.

    Try this:

    select year(dateadd(today plus 1 month)),
    month(dateadd(today plus 1 month))
    union
    select year(dateadd(today plus 2 month)),
    month(dateadd(today plus 2 month))
    union
    select year(dateadd(today plus 3 month)),
    month(dateadd(today plus 3 month))
    union
    select year(dateadd(today plus 4 month)),
    month(dateadd(today plus 4 month))
    union
    .... etc until 10.


    What do you think ?

    Good Luck

    rLyra


  7. #7
    Nirit Touboul Guest

    Re: Get 10 months forward with select statment


    Thank you All, you were very helpful!!!

    Nirit

  8. #8
    curtis king Guest

    Re: Get 10 months forward with select statment


    select convert(datetime,convert(char(2),datepart(month,delivery_period_dt))+'/1/'+convert(char(4),datepart(year,delivery_period_dt)))
    as delmo, curve_num, curve_value, effective_dt, '30' as month
    from "database name"
    where delivery_period_dt in (convert(datetime,convert(char(2),datepart(month,dateadd(month,30,effective_dt)))+'/1/'+convert(char(4),datepart(year,dateadd(month,30,effective_dt)))))
    and datename(weekday,effective_dt) not in ('Saturday', 'Sunday')
    group by convert(datetime,convert(char(2),datepart(month,delivery_period_dt))+'/1/'+convert(char(4),datepart(year,delivery_period_dt))),
    curve_num, curve_value, effective_dt
    PRINT '<<<Descriptive Detail Prompt+30>>>'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center