Get data from all days of the months in the query range

Asked

Viewed 404 times

1

I have a query in SQL Server that returns me the data from 1 ago

SELECT titulo, categoria
FROM posts 
WHERE dataCadastro BETWEEN DATEADD(year,-1,GETDATE()) AND GETDATE() 
ORDER BY dataCadastro DESC";

The problem is I need this consultation to get back to me too all data of all months in the range. I want the results to come from the first day of the last month in the range up to the current date.

The problem is that the exact results are coming 1 year ago so the results that are coming since the day 22/01/2017. I need the data provided 01/01/2017.

Use the DATEADD is best in this case?

sql-server

  • "I need the data since 01/01/2017" This date is fixed? If it is, you can set it directly in the code: BETWEEN CAST('20170101' AS SMALLDATETIME) AND GETDATE()

  • No, it’s not a fixed date. I need it to always come from the first day of the last month. If a year ago is 22/01 then I need you to come 01/01. If it was 15/03, I need you to come 01/03.

  • How the column is declared dataCadastro: date? datetime? another format?

  • @Dichrist would be that? How to show every day between two dates?

2 answers

3


Here’s a solution:

-- código #1 v2
-- define periodo de emissão
declare @Hoje date, @UmAnoAtras date;
set @Hoje= cast(current_timestamp as date);
set @UmAnoAtras= dateadd(month, datediff(month, 0, dateadd(year, -1, @Hoje)), 0);

--
SELECT titulo, categoria
  from posts 
  where dataCadastro between @UmAnoAtras and @Hoje;

The final version depends on how the column is declared dataCadastro.

The calculation of the variable @Umanoatras can be understood by the detail below:

declare @P1 int, @P2, int;
-- calcula data de um ano antes
set @P1= dateadd(year, -1, @Hoje);  
-- calcula quantos meses desde a data 0, o que elimina os dias
set @P2= datediff(month, 0, @P1);
-- soma número de meses à data 0
set @UmAnoAtras= dateadd(month, @P2, 0);

It is an efficient calculation as it does not involve type conversion.

  • This solution fits my case :)

1

A way for you to get to that starting day that you need would be this:

cast(cast(year(DATEADD(year, -1, GETDATE())) as varchar) + '-' + cast(month(GETDATE()) as varchar) + '-01' as smalldatetime)

So your query can stay:

declare @dataIni smalldatetime
select @dataIni = cast(cast(year(DATEADD(year, -1, GETDATE())) as varchar) + '-' + cast(month(GETDATE()) as varchar) + '-01' as smalldatetime)

SELECT titulo, categoria
FROM posts 
WHERE dataCadastro BETWEEN @dataIni AND GETDATE() 
ORDER BY dataCadastro DESC";

Browser other questions tagged sql-server

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