-1

I checked Get the records of last month in SQL server and it did not work!

I try to get the records of last month based on my database table and column issue_date.

What's the SQL query to do this?

For clarification, today (27-April-18) I want to get all records from March-18.

I have the issue_date in the format that I convert to date but below code gives me all records from 01-March-2018 to and including today. 

DATEPART(month, CONVERT (VARCHAR(11),DATEADD(day,wo_header.issue_date,`1971/12/31`),106)) = DATEPART(month, DATEADD(month, -1, getdate()))
Kalenji
  • 401
  • 2
  • 19
  • 42
  • can you not get it by one month ago like following?? WHERE issue_date >= GETDATE()-30 – Ghazni Apr 27 '18 at 14:11
  • -30 is for number of days wright. What I am trying to achieve is to have a code that will give me March data when run in April (every day - so it can not relay on days) – Kalenji Apr 27 '18 at 14:14
  • so you want data from 1st March to 31st March? – Ghazni Apr 27 '18 at 14:15
  • In this case yes. But in May it moves to 1st Apr to 30th Apr etc...always last month as per my question. – Kalenji Apr 27 '18 at 14:17
  • I think this post would give you an idea. https://stackoverflow.com/a/8458350/7100903 – schikkamksu Apr 27 '18 at 14:21
  • Possible duplicate of [Get the records of last month in SQL server](https://stackoverflow.com/questions/1424999/get-the-records-of-last-month-in-sql-server) – Tab Alleman Apr 27 '18 at 14:55

3 Answers3

0

To get firstDay and lastDay of previous month

DECLARE @FirstDayOfLastMonth     DATETIME = CONVERT(DATE, DATEADD(d, -(DAY(DATEADD(m, -1, GETDATE() - 2))), DATEADD(m, -1, GETDATE() - 1))),
        @LastDayOfLastMonth      DATETIME = CONVERT(DATE, DATEADD(d, -(DAY(GETDATE())), GETDATE()))

        SELECT @FirstDayOfLastMonth, @LastDayOfLastMonth

Your required Query

 SELECT *
       FROM   TABLE
       WHERE  CAST(issue_date AS DATE) BETWEEN CONVERT(DATE, DATEADD(d, -(DAY(DATEADD(m, -1, GETDATE() - 2))), DATEADD(m, -1, GETDATE() - 1))) AND CONVERT(DATE, DATEADD(d, -(DAY(GETDATE())), GETDATE()))
Ghazni
  • 826
  • 8
  • 16
  • Thanks Ghazani. I am afraid I am not able to declare any variables. It is some kind of report designer environment that needs to start with select statement. – Kalenji Apr 27 '18 at 14:27
  • in second query, i did not used any variable. – Ghazni Apr 27 '18 at 14:30
0

Perhaps the easiest method is eomonth(). If there is no time component on issuedate:

where issuedate > eomonth(getdate(), -2) and
      issuedate <= eomonth(getdate(), -1)

If there is a time component:

where issuedate >= dateadd(day, 1, cast(eomonth(getdate(), -2) as date)) and
      issuedate < dateadd(day, 1, cast(eomonth(getdate(), -1) as date))

Without eomonth, I would do:

where issuedate < cast(dateadd(day, 1 - day(getdate()), getdate()) as date) and issuedate >= dateadd(month, -1, cast(dateadd(day, 1 - day(getdate()), getdate()) as date))

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Still nil luck. The SQL hybrid I use is very restricted and eomonth is not available. What I try to do now is to exact the month from the date `(Month(CONVERT (VARCHAR(11),DATEADD(day,wo_header.closing_date,`1971/12/31`),106)))` and add WHERE condition `(Month(CONVERT (VARCHAR(11),DATEADD(day,wo_header.closing_date,`1971/12/31`),106)))' = '(Month(CONVERT (VARCHAR(11),DATEADD(day,wo_header.closing_date,`1971/12/31`),106)))' - 1`. It is not working yet thou. – Kalenji Apr 27 '18 at 15:20
0

The code I figured out is not pretty but it works. It first adds extra column with the month number to the SELECT portion of my code:

Month(CONVERT (VARCHAR(11),DATEADD(day,wo_header.closing_date,'1971/12/31'),106))  As Month

And than is used for WHERE statement:

Month(CONVERT (VARCHAR(11),DATEADD(day,wo_header.closing_date,'1971/12/31'),106)) = month(getdate())-1

So for anyone like me working in SQL report kind-of environment it should work.

Kalenji
  • 401
  • 2
  • 19
  • 42