Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5740
  • Last Modified:

SQL - Get the previous month's month and date

Hello,

I am using SQL Server Query Analyzer.  So, in SQL is it possible to get the previous month's year and date?  So, for example, when the current month is October how can I get September 2004 and then store it as a value?  But here is the kicker....I would like to convert the value to '200409'?

My current code is:
SELECT * FROM Table WHERE datereceived LIKE '200409%'

I would like it to be
SELECT * FROM Table WHERE datereceived LIKE 'lastmonthvariable%'

Thanks in advance
0
rudyflyer
Asked:
rudyflyer
  • 5
  • 4
  • 2
1 Solution
 
waelothmanCommented:
SELECT * FROM Table WHERE month(datereceived) = month(dateadd("m",-1,GetDate()) and year(datereceived) = year(dateadd("m",-1,GetDate())
0
 
rudyflyerAuthor Commented:
I got this error:

Arithmetic overflow error converting expression to data type datetime.

The field, datereceived, is numeric(8) and an example of the values in there is '20040910'

0
 
pique_techCommented:
In SQL, you can't have the quotes in the dateadd function.  So waelothman's suggestion is almost correct, but should be:

SELECT * FROM Table WHERE month(datereceived) = month(dateadd(m,-1,GetDate()) and year(datereceived) = year(dateadd(m,-1,GetDate())

rudyflyer:  did you need to store a value of YYYYMM for any reason other than to make searching easier?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
waelothmanCommented:
SORRY I MISSED NO "" IN M
0
 
rudyflyerAuthor Commented:
I get this error now

Incorrect syntax near the keyword 'and'.
0
 
pique_techCommented:
To have proper syntax, you'd need a couple more parentheses too, like so:

SELECT * FROM Table WHERE month(datereceived) = month(dateadd(m,-1,GetDate())) and year(datereceived) = year(dateadd(m,-1,GetDate()))

But with the numeric format in which your dates are stored, I don't think the DateAdd approach will work for you.

Try this instead:

SELECT * FROM Table
WHERE datereceived / 10000 =  year(dateadd(m,-1,GetDate())) AND
(datereceived % 10000) / 100 = month(dateadd(m,-1,GetDate()))



0
 
rudyflyerAuthor Commented:
I'm sorry guys but another error.  I used the following code:

SELECT * FROM Table
WHERE datereceived / 10000 =  year(dateadd(m,-1,GetDate())) AND
(datereceived % 10000) / 100 = month(dateadd(m,-1,GetDate()))

And got these errors:
Server: Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Line 1
Operand data type numeric is invalid for modulo operator.
0
 
pique_techCommented:
I was able to recreate this error when I used the numeric(8) datatype.  I was able to fix it with

SELECT * FROM Table
WHERE cast(datereceived AS integer) / 10000 =  year(dateadd(m,-1,GetDate())) AND
(cast(datereceived AS integer) % 10000) / 100 = month(dateadd(m,-1,GetDate()))

Sorry, I should have tested better before posting.  I tested it the first time with the integer datatype even though you said you had numeric(8) datatype--I erroneously assumed they'd be interchangeable.
0
 
rudyflyerAuthor Commented:
pique_tech,

this is perfect.  thanks.  Now, I am adding another 50 points to the question to see if you can help me with this.  Later in my code I then want to delete everything that is not last month's records.  here is my code:

delete from table
where datereceived NOT LIKE '200409%'

It is static but I would like to implement your code into this.

Thanks a lot
0
 
pique_techCommented:
Now that we have the details of your particular field worked out...  ; )

I think that what you want would be as easy as

DELETE FROM Table
WHERE cast(datereceived AS integer) / 10000 <>  year(dateadd(m,-1,GetDate())) AND
(cast(datereceived AS integer) % 10000) / 100 <> month(dateadd(m,-1,GetDate()))

First, test that this SELECTS the correct records by trying

SELECT * FROM Table
WHERE cast(datereceived AS integer) / 10000 <>  year(dateadd(m,-1,GetDate())) AND
(cast(datereceived AS integer) % 10000) / 100 <> month(dateadd(m,-1,GetDate()))

Let me know how this works out for you.
0
 
rudyflyerAuthor Commented:
Thanks a lot for your hard work.  Appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now