Previous dates

Asked

Viewed 162 times

1

I am making report in which I need to always get the date of customer registration in a previous month.
Example: I am in the month of December 2017, I want to pick up customers who were registered from November 1 to the last day of November; And when I have in January 2018, I want to pick up customers who have been registered from December 1st until the last day.

sql sql-server
  • 1

    What is the structure of your table?

  • Tables are made by the Entity Framework

  • Post what you already have of code, html, css, js etc. this helps you answer correctly.

2 answers

0

If you do this by query here resolves, you will always get the start date and end date of the month before the current.

Select Convert(DateTime, Convert(Varchar(6), DateAdd(Month, -1, GetDate()), 112) + '01', 103) -- data inicio 
Select DateAdd(Day, -1, DateAdd(Month, 1, Convert(DateTime, Convert(Varchar(6), DateAdd(Month, -1, GetDate()), 112) + '01', 103))) -- data fim

-1


To select customers registered in the previous month your code will be next to the examples below:

Entity Framework

db.Usuarios.Where(x => x.DataCriacao.Month == DateTime.Now.AddMonths(-1).Month);

SQL Server (T-SQL)

SELECT 
    * 
FROM 
    Tabela 
WHERE 
    Canal = 'FILIAIS' 
    AND Email like '%@%' 
    AND DATEPART(mm, DataCadastro) = DATEPART(mm, DATEADD(mm, -1, GETDATE())
    AND DATEPART(aaaa, DataCadastro) = DATEPART(aaaa, DATEADD(mm, -1, GETDATE())

  • I did something like this: AGE.Datacadastro BETWEEN GETDATE()-30 AND DATEADD(MM,DATEDIFF(MM,-1, GETDATE(),-1)

  • I put an example using SQL Server

  • I’ll copy my Where.. rs WHERE Channel = 'AFFILIATES' AND Email is not null AND Email <> '' AND Email like '%@%' AND AGE.Datacadastro BETWEEN GETDATE()-30 AND DATEADD(MM,DATEDIFF(MM,-1, GETDATE()),-1)

  • I updated the answer, I left only LIKE with @, it already deletes the other filters.

  • This your DATEPART('mm', AGE.Datacadastro) This MM wouldn’t be without simple quotes? To pass the MM ( mes)? Put without going through with simple quotes and it worked! Thanks friend!.

  • Only had a small problem, in his example he also caught the year 2016, it has to be only this year?

  • Exactly, I made the adjustments.

Show 2 more comments

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.