Get 10 months forward with select statment

[Nirit Touboul]

Hi,

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

Tahnk you,

Nirit

[D. Patrick Hoerter]

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

[Nirit Touboul]

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
>
>

[James T. Stanley]

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
> >
> >
>

[..]

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.

[rLyra]

"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

[Nirit Touboul]

Thank you All, you were very helpful!!!

Nirit

[curtis king]

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>>>'