Solved

SQL - Get the previous month's month and date

Posted on 2004-10-13
11
5,563 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

13 Experts available now in Live!

Get 1:1 Help Now