How to get data for the last month and data for today

Question

Need help writing a request to get data for the last month, as well as data for the current day.

If today’s date is March 23, 2011, I need to get data for the last month and data before today (March 23, 2011).

If the date is April 3, 2011, the data should consist of data for March and data until April 3, 2011.

Thanks,

Shahsra

Answer 1

Today including time info : getdate() Today without time info : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0) Tomorrow without time info : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1) Beginning of current month : DATEADD(month, datediff(month, 0, getdate()), 0) Beginning of last month : DATEADD(month, datediff(month, 0, getdate())-1, 0) 

therefore, most likely

 WHERE dateColumn >= DATEADD(month, datediff(month, 0, getdate())-1, 0) AND dateColumn < DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1) 

Answer 2

Return for one month, subtract the number of days before the current date and add one day.

 WHERE DateField <= GetDate() AND DateField >= DateAdd( mm, -1, DateAdd(dd, -1*DatePart(dd, GetDate())+1, GetDate()) ) 

To quickly remove the time, you can use this Cast (Floor (Cast (GETDATE () AS FLOAT)) AS DATETIME)

So the second part will be (no time)

 DateField >= Cast( Floor( Cast( (DateAdd( mm, -1, DateAdd(dd, -1*DatePart(dd, GetDate())+1, GetDate()) )) AS FLOAT ) ) AS DATETIME ) 

Answer 3

Very useful page.

 declare @d datetime = '2011-04-03'; declare @startDate datetime; select @startDate = CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,@d),113),8) AS datetime); select @startDate; 

Answer 4

 Select Column1, Column2 From Table1 Where DateColumn <= GetDate() AND DateColumn >= DATEADD(dd, - (DAY(DATEADD(mm, 1, GetDate())) - 1), DATEADD(mm, - 1, GetDate())) 

Edit: +1 to Russell Steve. I sent a message before I found out that he posted.

Answer 5

Just curious how this solution will work for Redshift? WHERE dateColumn> = DATEADD (month, datediff (month, 0, getdate ()) -1, 0) AND dateColumn <DATEADD (DAY, DATEDIFF (day, 0, getdate ()), 1)