Service 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

General SQL Server Forums

New to SQL Server Programming

Service Month

Author

Topic

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-02-06 : 15:46:38

How do I do a max month minus 1?
This is my code, but it's not coming up with the result I want.
distint svcmo(service month)
200801
200712
200711
200710

select max(svcmo) from my table
result: 200801
select max(svcmo) - 1 from my table
result: 200800

I need to the result to be 200712, not 200800.

Any help?????????????

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-06 : 15:53:47

Classic reason why you should be using a DATETIME or SMALLDATETIME column to hold dates. What is the datatype of [svcmo]?

Be One with the Optimizer
TG

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 16:01:43

SELECT DATEPART(yy, DATEADD(mm, svcmo%100-1, DATEADD(yy, svcmo/100-1900, 0))-1)*100+DATEPART(mm, DATEADD(mm, svcmo%100-1, DATEADD(yy, svcmo/100-1900, 0))-1)

Edit: Im assuming that svcmo is INT datatype.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-02-06 : 16:12:17

What about something like this:

declare @dt int
select @dt = 200801

SELECT convert(char(6),dateadd(mm, -1, convert(datetime,right(@dt, 2)+'-01-'+left(@dt, 4))),112)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 03:38:38

or this ?
only 2 function call

DECLARE @dt int
SELECT 	@dt = 200801

SELECT	DATEADD(MONTH, (@dt % 100) - 2, DATEADD(YEAR, (@dt / 100) - 1900, 0))

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-11 : 11:44:42

Or you could do it without date math:

SELECT MAX(svcmo) 
FROM MyTable
WHERE svcmo < (SELECT MAX(svcmo) FROM MyTable)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 15:05:23

Only if the at least one record is present with the wanted month...

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-11 : 17:11:28

quote:
Originally posted by Peso

Only if the at least one record is present with the wanted month...

Ahh true, I mis-read the requirement as getting the 2nd greatest record.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources