I need to insert/get only month and year

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

Transact-SQL (2000)

I need to insert/get only month and year

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-05 : 04:34:10

Hello,

I need to insert in a table field only month and year. Which is the best solution to do so? I want this field in a datetime or smalldatetime format so that later I can order this field properly in an Excel pivot table.
Specifically I need to insert in the table previous month of the current date.

Thank you,
Cèsar

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-05 : 05:19:43

The best solution is enter a complete date although I don’ t need the day part? For example: 31/01/2007 as January 2007, 28/02/2007 as February 2007, etc... ?

Any suggestions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 05:19:47

Make it first day for each month

Previous month's first day based on today

select dateadd(month,datediff(month,0,getdate())-1,0)

or

first day of this month

select dateadd(month,datediff(month,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-05 : 05:23:58

Hello Madhivanan,
Do you think it is better get the first day of the month instead of the last one? If so, why?

thanks

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-05 : 05:48:52

If the problem is the code this one gets the date at the end of previous month (without time part):


SELECT DATEADD(mm, -1, DATEADD(dd, -DAY(DATEADD(mm,1,DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())))),DATEADD(mm,1,DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())))))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 06:16:12

quote:
Originally posted by cesark

If the problem is the code this one gets the date at the end of previous month (without time part):

SELECT DATEADD(mm, -1, DATEADD(dd, -DAY(DATEADD(mm,1,DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())))),DATEADD(mm,1,DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())))))

or use

select dateadd(month,datediff(month,-1,getdate())-1,-1)

Madhivanan

Failing to plan is Planning to fail

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-05 : 06:36:51

Your code works, and looks really simple and clear

.. But I don’ t understand it..

What does ‘datediff(month,-1,getdate())-1’ ? It returns this integer number ‘1293’..
And what does the last ‘-1’? ‘... ,-1)’

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 07:26:49

quote:
Originally posted by cesark

Your code works, and looks really simple and clear .. But I don’ t understand it..

What does ‘datediff(month,-1,getdate())-1’ ? It returns this integer number ‘1293’..
And what does the last ‘-1’? ‘... ,-1)’

Thanks

Well. You dont two -1s. Simply use

select dateadd(month,datediff(month,0,getdate()),-1)

select dateadd(month,datediff(month,0,getdate()),0) gives you first day of this month and adding -1 day gives last day of previous month

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-05 : 07:44:51

I recommend using the first day of the month, it's easier.

Also, if you use the first day of the month, it is easy to put a constraint on your column:

check DatePart(day, YourDate)=1

to ensure that all dates are always the first day of the month.

- Jeff
http://weblogs.sqlteam.com/JeffS

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-05 : 09:15:24

Hi Jeff, I find interesting your opinion (this is what I looked for). So, I think I will use the first code that Madhivanan wrote:

quote:

Previous month's first day based on today

select dateadd(month,datediff(month,0,getdate())-1,0)

Do you agree?

Thanks to both!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources