Period variable data SQL Server

Question

Period variable data SQL Server

Navigation

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

1

Below I have the following code where I search in a table all the data within the specified period in the declared variables. However, a specific customer has a different month closure, in which case his month starts on the 26th, and ends on the 25th.

I'm trying to make the variables bring this period but to no avail!

In this case, the variable @periodoInicial would have to start in 2017-07-26 and the variable @periodoFinal in 2017-08-25 , and after this period, the variables already changed to 2017-08-26 to 2017-09-25 , it is possible do that?

Code:

declare @periodoInicial as date,
        @periodoFinal as date
set @periodoInicial = cast(dateadd(mm,-1,dateadd(dd,-day(getdate())+1,getdate())) as date)
set @periodoFinal = cast(dateadd(dd,-day(getdate()),getdate()) as date)

SELECT * FROM productionEnvironmentInvoiceData
where proposalDate between @periodoInicial and @periodoFinal

NOTE: I'M WITH SQL SERVER 2008

sql sql-server

asked by anonymous 02.08.2017 / 15:36

2 answers

1

In this case, the initial @period variable would have to start in 2017-07-26 and the variable @periodo End in 2017-08-25, and ending this period, the variables would already change to 2017-08-26 until 2017-09-25, is it possible to do this?

If you want to collect monthly periods, considering a longer period of months, here is an approach. First you must define the complete period of issue, which involves several months. The code is responsible for generating each month, then defining the values of the @periodoInicial and @Piodiodo variables as requested.

-- código #1 v3
--> informe o dia inicial do período completo de emissão (formato dd/mm/aaaa)
declare @MêsInicial date, @MêsFinal date;
set @MêsInicial= convert(date, '26/7/2017', 103);
set @MêsFinal= convert(date, '26/8/2017', 103);

-- variáveis para emissão mensal
declare @periodoInicial date, @periodoFinal date;

-- início do primeiro mês
set @periodoInicial= @MêsInicial;

while @periodoInicial <= @MêsFinal
  begin
  -- calcula o final do mês
  set @periodoFinal= dateadd(day, -1, dateadd(month, +1, @periodoInicial));
  -- processamento do mês
  SELECT colunas 
    from productionEnvironmentInvoiceData
    where proposalDate between @periodoInicial and @periodoFinal;
  -- próximo início de mês
  set @periodoInicial= dateadd(month, +1, @periodoInicial);
  end;

02.08.2017 / 22:06

When to use Html.BeginForm () and why to use? How to change position of all matrix elements by changing the row number by column?

score 2 · Accepted Answer

If you are using version 2012 or higher of SQL Server the following code will meet the specification:

DECLARE @dia    INT;
DECLARE @inicio DATE;
DECLARE @fim    DATE;

SET @dia = 26;

SET @inicio = DATEADD(DAY, @dia, EOMONTH(GETDATE(), -2));
SET @fim = DATEADD(DAY, -1, DATEADD(MONTH, 1, @inicio));

SELECT *
  FROM ProductionEnvironmentInvoiceData peid
 WHERE peid.proposalDate BETWEEN @inicio AND @fim;

For versions prior to 2012 the adapted code would look like this:

DECLARE @dia int;
DECLARE @inicio date;
DECLARE @fim date;

SET @dia = 26;

SET @inicio = DATEADD(day, @dia - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -1, GETDATE())), 0));
SET @fim = DATEADD(day, -1, DATEADD(month, 1, @inicio));

SELECT *
  FROM productionenvironmentinvoicedata peid
 WHERE peid.proposaldate BETWEEN @inicio AND @fim;

DATEADD

Returns a specified% s of the% specified by the% specified% s (signed integer) added to the specified% s of this% s.

date

Returns the last day of the month containing the specified date with an optional offset.