Period yyyy.mm back one month - SQL Server Forums

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)

Period yyyy.mm back one month

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-08-04 : 05:20:06

Hi,

I have a period field being yyyy.mm but i would like to go back one month.
eg. Value = 2009.01 I want result 2008.12

Please Assist!

Regards

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 05:25:33

what's the data-type used for the column? I take it you decided not to use DATETIME?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-04 : 05:25:47

Why dont you use proper datetime datatype to store dates?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-04 : 05:36:02

This is the Accounting Period ?

I believe you should have a table to store your accounting period ? If so you can just simply


select max(period)
from   yourtable
where  period < @period

If not . . convert to datetime and then convert back


declare @period	varchar(10)

select	@period = '2009.01'

select	stuff(convert(varchar(6), dateadd(month, -1, convert(datetime, @period + '.01')), 112), 5, 0, '.')

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-04 : 05:37:51

Another way

declare @date varchar(7)
set @date='2009.01'
select left(replace(convert(varchar(10),CAST(@date+'.01' as datetime)-1,111),'/','.'),7)

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-08-04 : 05:48:06

Struggliing a bit... Please Assist

It is a decimal data type,I tried the following:

declare @date varchar(7)
set @date = select Period from table where Type = 'SALES'
select left(replace(convert(varchar(10),CAST(@date+'.01' as datetime)-1,111),'/','.'),7)

Thank You All

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-04 : 06:00:42

declare @date varchar(7)
set @date = (select max(Period) from table where Type = 'SALES')
select left(replace(convert(varchar(10),CAST(@date+'.01' as datetime)-1,111),'/','.'),7)

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-08-04 : 06:15:42

This great stuff.

Thank You All for the assistance! :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 07:01:59

And if you use style 102 instead of 111, you don't need the replace function nor left function

SELECT	CONVERT(CHAR(7), CONVERT(DATETIME, @Date + '.1', 102) - 1, 102)

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-04 : 07:02:55

quote:
Originally posted by ismailc

This great stuff.

Thank You All for the assistance! :)

You are welcome

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 07:44:33

And if @Date is DECIMAL(6, 2), try

SELECT	CONVERT(CHAR(7), CONVERT(DATETIME, STR(@Date, 7, 2) + '.1', 102) - 1, 102)

This also works if @Date is CHAR(7).

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 07:48:00

[code]DECLARE @Sample TABLE
(
c CHAR(7),
d DECIMAL(6, 2)
)

INSERT @Sample
SELECT '2009.01', 2009.01 UNION ALL
SELECT '2009.02', 2009.02 UNION ALL
SELECT '2009.12', 2009.12

SELECT c,
CONVERT(CHAR(7), CONVERT(DATETIME, STR(c, 7, 2) + '.1', 102) - 1, 102) AS ConvertedC,
d,
CONVERT(CHAR(7), CONVERT(DATETIME, STR(d, 7, 2) + '.1', 102) - 1, 102) AS ConvertedD
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources