Solved

SQL - Get the previous month's month and date

Posted on 2004-10-13
11
5,559 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now