?
Solved

SQL - Get the previous month's month and date

Posted on 2004-10-13
11
Medium Priority
?
5,700 Views
Last Modified: 2011-08-18
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
Comment
Question by:rudyflyer
  • 5
  • 4
  • 2
11 Comments
 
LVL 5

Expert Comment

by:waelothman
ID: 12303966
SELECT * FROM Table WHERE month(datereceived) = month(dateadd("m",-1,GetDate()) and year(datereceived) = year(dateadd("m",-1,GetDate())
0
 

Author Comment

by:rudyflyer
ID: 12304058
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12304066
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:waelothman
ID: 12304094
SORRY I MISSED NO "" IN M
0
 

Author Comment

by:rudyflyer
ID: 12304100
I get this error now

Incorrect syntax near the keyword 'and'.
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12304170
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
 

Author Comment

by:rudyflyer
ID: 12304202
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12304266
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
 

Author Comment

by:rudyflyer
ID: 12304305
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
 
LVL 12

Accepted Solution

by:
pique_tech earned 1200 total points
ID: 12304323
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
 

Author Comment

by:rudyflyer
ID: 12304519
Thanks a lot for your hard work.  Appreciate it.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question