Convert month into its number equivalent...(Ex. JA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert month into its number equivalent...(Ex. JA

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-06-19 : 06:54:48

I want to insert the 12 rows starting with 1 for jan, 2 for feb...12 for dec..
Insert into tableorders(OrdMonth,createddate)values(1,getdate())

Thank you very much for the helpful info.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 07:12:06

That's great! You should do that.

Jim

Is there something you need help with?

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-19 : 07:13:01

I'm not able to understand your question.. can you provide us more information?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-19 : 07:13:08

[code]
Insert into tableorders(OrdMonth,createddate)
SELECT NUMBER, getdate()
FROM F_TABLE_NUMBER_RANGE(1,12)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-21 : 21:20:06

Alternatives:

INSERT INTO dbo.TableOrders(OrdMonth, CreateDate)
SELECT Number, GETDATE()
  FROM Master.dbo.spt_Values
 WHERE Type = 'P'
   AND Number BETWEEN 1 AND 12

INSERT INTO dbo.TableOrders(OrdMonth, CreateDate)
SELECT TOP (12)
       ROW_NUMBER() OVER (ORDER BY GETDATE())r, GETDATE()
  FROM Master.sys.SysObjects

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-22 : 00:15:28

Hi, Also try this once

declare @startmonth datetime,@endmonth datetime
select @startmonth = '1/1/2009' ,@endMonth = '12/31/2009'

SELECT DATEADD(DAY, number, @startmonth)
FROM Master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @startmonth) <= @endMonth
AND datepart(d,DATEADD(DAY, number, @startmonth)) = datepart(m,DATEADD(DAY, number, @startmonth))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-22 : 00:19:42

quote:
Originally posted by Nageswar9

Hi, Also try this once

declare @startmonth datetime,@endmonth datetime
select @startmonth = '1/1/2009' ,@endMonth = '12/31/2009'

SELECT DATEADD(DAY, number, @startmonth)
FROM Master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @startmonth) <= @endMonth
AND datepart(d,DATEADD(DAY, number, @startmonth)) = datepart(m,DATEADD(DAY, number, @startmonth))

OP wanted it by month not day.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources