ADD_MONTH equivalent in SQL 2000 1

[bnye]

All,

I am new to SQL, and would like to select records where the date corresponds to todays date - 1 month.

I have heard of ADD_MONTH in other platforms, but that doesn't help me out in this SQL 2000 case.

The statement that works for todays year - 1 year is:

SELECT SUM(premium) FROM PRODUCTION WHERE Year(Statementdate) = Year(GetDate() - 1)

What doesn't work for todays date - 1 month is:

SELECT SUM(premium) FROM PRODUCTION WHERE Year(Statementdate) = Year(GetDate()) AND Month(Statementdate) = Month(GetDate() - 1)

This statement returns nothing, but doesn't fail.
If anyone has any ideas for this novice SQL programmer it would be appreciated.

Thanks,
bnye

 

[tlbroadbent]

Select LastMonth=dateadd(m,-1,getdate()) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

SELECT SUM(premium)
FROM PRODUCTION
WHERE Year(Statementdate) = Year(GetDate())
AND Month(Statementdate) = Month(GetDate() - 1)


terry,
i was wondering if he is going to run into any time-related issues. i.e. it looks like he might try to do:

SELECT SUM(premium)
FROM PRODUCTION
WHERE StatementDate = dateadd(m,-1,getdate())

If he does, will that work for him?

 

[bnye]

Terry and bperry,

Thanks for your resposnses.

That did not seem to return any values, although I know values exist. Is there another route that I should look into.

Thanks,
bnye

 

[bperry]

Hi byne:
terry doesn't seem to be available right, but I'll make this suggestion (untested) for you if you are in a rush. He might propose something else when he gets back to you.

SELECT SUM(premium)
FROM PRODUCTION
WHERE
Convert(varchar(8),StatementDate,112) =
Convert(varchar(8),dateadd(m,-1,getdate()),112)

 

[tlbroadbent]

Note 1: Month(getdate()-1) subtracts 1 day then finds the month. You should use Month(getdate())-1 which determines the current month and then subtracts 1.

Note 2: The following query doesn't work if the current month is January because the prior year will be 1 less than the current year and the result of Month()-1 will be 0.

SELECT SUM(premium)
FROM PRODUCTION
WHERE Year(Statementdate) = Year(GetDate())
AND Month(Statementdate) = Month(GetDate()) - 1

The following will work.

SELECT SUM(premium)
FROM PRODUCTION
WHERE Year(Statementdate) = Year(dateadd(m,-1,GetDate()))
AND Month(Statementdate) = Month(dateadd(m,-1,GetDate()))

I apologize that I ignored most of the original post in my first reply and didn't address the entire question. I was in hurry. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bnye]

Terry and bperry,

Thank you both for the responses. I like both ideas, but chose to go with Terry's in order to keep the date object provided by SQL. It is working great! I am not sure how liberal useres are supposed to be with "Most helpful" marks so I will only designate one.


Kudos to you both,
bnye