Get daily data from the months in the query range

Question

Get daily data from the months in the query range

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I have a query in SQL Server which returns me the data of 1 ago

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

The problem is that I need this query to also return all data for every month in the range. I want the results to come from first day of last month in the range to the current date.

The problem is that the results are coming exactly 1 year ago so the results that are coming in come from 22/01/2017. I need the data to come in 01/01/2017 .

Is DATEADD best in this case?

sql-server

asked by anonymous 22.01.2018 / 17:30

2 answers

1

One way for you to get to that initial day 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";

22.01.2018 / 22:06

How to add auto scrolling in an html div? How does Length and Out of For in Javascript work?

score 3 · Accepted Answer

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 dataCadastro column is declared.

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);

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