-1

I am trying to fetch data for the previous full month, but the data from the last day is not fetching.

Output: Previous Month is April and this condition only retrieving data up to 29th April. Data for 30th April is missing.

Can please someone help me to correct this.

OLH.DateStamp > CONVERT(VARCHAR,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0),101)
And 
OLH.DateStamp < CONVERT(VARCHAR,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1),101))

Thanks in advance

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
wahab
  • 51
  • 1
  • 9
  • If your values include *time*, it's usually better to compute an *exclusive* endpoint (I.e. the first of the current month) and use a `<` rather than `<=` in the comparison. – Damien_The_Unbeliever May 25 '17 at 10:39
  • Possible duplicate of [SQL Query to find the last day of the month](https://stackoverflow.com/questions/16646585/sql-query-to-find-the-last-day-of-the-month) – Tab Alleman May 25 '17 at 12:57

2 Answers2

1

The simplest way to get data for the previous month is:

DATEDIFF(month, OLH.DateStamp, GETDATE()) = 1

However, that does not use indexes. So, a better method is:

OLH.DateStamp >= DATEADD(MONTH, -1, DATEADD(DAY, 1 - DAY(GETDATE()), CAST(GETDATE() as DATE))) AND
OLH.DateStamp < DATEADD(DAY, 1 - DAY(GETDATE()), CAST(GETDATE() as DATE))

The expression:

DATEADD(DAY, 1 - DAY(GETDATE), CAST(GETDATE() as DATE))

returns midnight on the first day of the current month.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You should try last day of Month Should be <= OLH.Datestamp

To get Desired Output you are Looking for:

--Use It In Where Clause 
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()))

OLH.DateStamp >=  CONVERT(VARCHAR, @FirstDayOfLastMonth , 101) AND
OLH.DateStamp <=  CONVERT(VARCHAR, @LastDayOfLastMonth , 101)
Tanner
  • 22,205
  • 9
  • 65
  • 83
Alfaiz Ahmed
  • 1,698
  • 1
  • 11
  • 17