Solved

SQL - Get the previous month's month and date

Posted on 2004-10-13
11
5,610 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 300 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

695 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