Variable period date SQL Server

Asked

Viewed 1,654 times

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 client has a different month closure, in this case, his month starts on the 26th, and ends on the 25th.

I’m trying to make the variables bring this period but without success!

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

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 AM WITH SQL SERVER 2008

sql database sql-server

  • did not understand the " ending this period, the variables already changed to 2017-08-26 until 2017-09-25"

  • In fact a dia_de_closure column should be created in the client table, get this "day" and based on it do the calculation.

  • @Rovannlinhalis for example, the month in this case is not from day 01 until the 30 or 31, but starts on day 26 and ends in the 25, understands?

  • @Motta you say put a column 'periodoInicial' that repeats 26 in all lines and another with 26 'periodoFinal' and follow the calculation of the variables giving select in this table?

  • No, I mean column of event in the enterprise table sql would look something like SELECT * FROM productionEnvironmentInvoiceData , COMPANIES Where productionEnvironmentInvoiceData.CODEMPESA = COMPANIES.CODEMPESA AND proposalDate between CONVERT(DATETIME, ANO + MES-1 + diadefechamento+1) and CONVERT(DATETIME, ANO + MES + diadefechamento)

  • @jvbarsou, only one question, in the table productionEnvironmentInvoiceData the field is Date or Datetime ? If it is Datetime, interesting to use from day 26 to day 26, why being the final date 2017-08-26, there will be the question of hours and minutes.

  • How’s your client chart?

  • @Ricardosouza the field is date

  • What do you mean? @Sorack

  • @jvbarsou you get the day by customer registration, it is not?

  • @Sorack if I understood well what you meant, no, in the true I’m in the variable even the period because I will make a process in specific for this type of client. So much so that this table is just data from them.

  • @jvbarsou you know the version of your SQL Server?

  • @Sorack in case I am using sql server 2008

Show 8 more comments

2 answers

2


If you are using the 2012 or higher version 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 date specified with the range number specified (integer signed) added to datepart specified of that date.

EOMONTH

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

  • for 2008 version, how can I adapt the code?

  • @jvbarsou added to previous versions

  • 1

    It worked perfectly @Sorack, thank you very much!

1

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

If you want to collect monthly periods, considering a longer period of months, here’s an approach. The first is to define the full period of issuance, which involves several months. The code is in charge of generating each month, then setting the values of the variables @periodoInicial and @Periodofinal, 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;

Browser other questions tagged sql database sql-server

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