Month and year of date - 1 month etc

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)

Month and year of date - 1 month etc

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2009-11-04 : 17:28:54

Hi there,

Does anyone know how to take a date and seperate the month and year and then subtract a month? for example

Take: 01 Mar 2009
Make: 03 2009
Then subtract a month: 02 2009

I keep getting conversion errors. Any help would be great

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-11-04 : 18:11:28

[code]select
-- Create formatted string: MM YYYY
MM_YYYY = right(replace(convert(varchar(30),DT,103),'/',' '),7)
from
(
Select
-- Subtract one month
DT = dateadd(month,-1,DT)
from

( -- Convert string to Datetime
select Dt = convert(datetime,'01 Mar 2009')
) aa
) a
[/code]

Results:
[code]MM_YYYY
-------
02 2009[/code]

CODO ERGO SUM

cidr
Posting Yak Master

207 Posts

Posted - 2009-11-05 : 16:54:12

Thanks Michael, that worked a treat

Cheers
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources