Career 

How to specify an empty date in a request? 1s time in request.

Very often in 1C queries you have to work with dates. Especially when the request is made to metadata objects that contain periodic information. As a rule, these are registers (information, accumulation, calculation, accounting). Let's look at the most commonly used functions of the 1C query language for working with dates. We will build examples based on the information register EmployeesOrganizations ZUP configurations version 2.5.

  • DATE TIME

    Allows you to get a date in a request (with or without time) by specifying the year, month, day, hour, minute, second.
    Syntax:
    DATETIME(Year, Month, Day, Hour, Minute, Second)
    Usually the hour, minute and second are not indicated. Let's give a mini example. Enter the following text in the Query Console:

    SELECT DATETIME(2016, 1, 1)

    As a result of executing the request, we get the date - 01/01/2016
    In fact, it is difficult to imagine a situation in which the date would be indicated in this way in a request. After all, when you need to specify a period, parameters are used. But there is a case when this function is of particular value. This is when we need to indicate an empty date in the fields or in the request conditions. Let me remind you that for the 1C language empty date has the form - 0001.01.01. Thus, to get an empty date in a request, just specify DATETIME(1, 1, 1). As an example, let’s select from the information register EmployeesOrganizations records that are not filled in Completion Period:

    SELECT Organizational Employees.Period, Organizational Employees.Employee, Organizational Employees.Position, Organizational Employees.Organization Division FROM Register Information.Organizational Employees AS Organizational Employees WHERE Organizational Employees.Completion Period = DATETIME(1, 1, 1)

  • BEGINNING OF PERIOD

    For specified date returns the beginning of the period it enters.
    Syntax:
    START OF PERIOD(Date, PeriodType)
    PeriodType can take the following values: MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, DECADE, HALF-YEAR
    In the Query Console, enter:

    SELECT START OF PERIOD(DATETIME(2016, 1, 15), MONTH)

    The request will return - 01/01/2016
    And now an example. As you know, the periodicity of the register EmployeesOrganizations one day. Let's create a query in which the start date of the month will be displayed instead of the actual record period.

    SELECT BEGINNING OF PERIOD(Employees of Organizations.Period, MONTH) AS Beginning of Month, Employees of Organizations.Employee, Employees of Organizations.Position, Employees of Organizations.Division of Organization FROM Register Information.Employees of Organizations AS Employees of Organizations

  • END OF PERIOD

    The syntax is the same as for the beginning of the period. And as the name implies, it returns the end of the period by date and period type. We will not consider it in detail. Let's limit ourselves to a mini example.
    Request:

    SELECT END OF PERIOD(DATETIME(2016, 1, 15), MONTH)

    Returns 01/31/2016 23:59:59
    As you can see, the value is returned accurate to the second.

  • ADDKDATE

    Adds the specified number of time intervals to the date.
    Syntax:
    ADDKDATE(Date, PeriodType, Quantity)
    PeriodType takes the same values ​​as for the function BEGINNING OF PERIOD
    For example, let's choose the February date:

    SELECT ADDKDATE(DATETIME(2016, 2, 15), MONTH, 2)

    We get the date 04/15/2016 0:00:00 Despite the fact that February is a short month, the day of the received date is the same as the original one. It’s very convenient that you don’t have to think about the number of days in months.
    The quantity can also be negative. Then the interval is counted in the opposite direction.

  • DIFFERENCEDATE

    Calculate the difference between two dates in the specified units.
    Syntax:
    DATE DIFFERENCE (Start Date, End Date, Period Type)
    The period type can take the following values: SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER, YEAR
    For example:

    SELECT DIFFERENCEDATE(DATETIME(2016, 2, 15), DATETIME(2016, 3, 1), DAY)

    returns 15

Here we reviewed the most frequently used functions of the 1C query language. The rest are used quite rarely. If necessary, examples of working with them can be found in the help built into the 1C platform.

  43
 NULL – missing values. Not to be confused with zero value! NULL is not a number, does not equal a space, an empty reference, or Undefined. NULL is a type-forming value, i.e. there is a type NULL and a single value of this type. NULL...   26
 To generate and execute queries to database tables in the 1C platform, a special object of the Query programming language is used. This object is created by calling the New Request construct. Convenient request...   18
 The article provides useful techniques when working with 1C v.8.2 queries, as well as information that is not so well known about the query language. I'm not trying to give Full description query language, but I just want to dwell on...   13
 LIKE - Operator for checking a string for similarity to a pattern. Analogue of LIKE in SQL. The SIMILAR operator allows you to compare the value of the expression specified to the left of it with the pattern string specified to the right. The meaning of the expression...

All documents existing in 1C configurations, and, consequently, almost all registers must have at least one attribute with the Date type, which is why every developer needs to know and understand:

  • How to convert parameters of other types to the type in question;
  • How to determine an empty date in a 1C request;
  • What is the difference between a date and a time limit?

It is these questions that we will try to answer in our article.

What is a date and how to determine it

Since making most management decisions and keeping records does not require time accuracy exceeding 1 second, the developers of the 1C platform decided that this value would be the absolute minimum in the date format. Thus, each attribute describing the time of an event in the program must contain:

  • The year the event occurred;
  • Month of this event;
  • Day.

It is not necessary to indicate: hour, minute and second. If these three parameters are omitted and there are no additional conditions, the program automatically sets the time to the beginning of the day.

The date formats existing in the world have significant differences:

  1. In Russia, we are accustomed to putting the day first, then the month of the event, and at the end the year;
  2. US residents start the date with the month;
  3. Czechs, Poles and Slovenians record periods in the Year – Month – Day format.

It is the latter format that the 1C platform uses.

Convert to date

In order to obtain a parameter with the Date type from several values ​​or from a string, you must use the code shown in Fig. 1

As can be seen from the figure above, you can determine the date either using one line or by splitting this line into its component parts using a comma, the result will not change.

It is important to understand that the year of the date must contain four digits, including the millennium and century of the event, the month, day, hours and seconds must be two characters long, including leading zeros.

The time countdown in the program starts from the beginning of the day on January 1, 0001. For the code above, this value can be determined in one of two ways (Figure 2).

Rice. 2

In the second line, we omitted the hours, minutes and seconds of the event, which did not at all affect the performance of our code.

Features of using date in 1C queries

For most data types used by the 1C platform, there are predefined void values. For numbers, this is 0; for links, you can define the value EmptyLink(); for a date, the empty value is usually considered to be the starting date; it is with this that the details of the corresponding type must be compared when setting the request parameters.

It is important to understand that even if no numbers are specified in the value of a form attribute that has the type in question, that is, the window looks like (Fig. 3), this does not mean that nothing is specified in it; comparison of this parameter with an empty string will not work.

Rice. 3

Having received an empty date, we can specify it as a parameter to our request, that is, use the construction (Fig. 4)

However, there are times when it is better to check inside the request body, without passing an empty date as a parameter. To do this, you can enter the appropriate condition in the request code (Fig. 5) and use the DateTime() request function.

Rice. 5

In the above request text, we omitted the leading zeros of the year, month and day, and also did not indicate the hours, minutes and seconds, and the program, as they say, ate up this assumption.

Date and time boundary

Another interesting fact related to the relationship between queries and dates is the use of the concept of “point in time” when accessing various database tables.

The “up to a millisecond” accuracy specified in the technical documentation when describing the primitive Date type is most clearly manifested when selecting records from the virtual tables of the accumulation register: if the accumulation register, in addition to the Turnover table, has the Remaining and Remaining and Turnover tables, then the selection from them carried out on certain time, may give different results.

To understand how and why this happens, consider a simple example:

  1. Before the sales document was carried out at 12 hours 31 minutes 36 seconds, the balances according to the Sugar nomenclature were 30 kg;
  2. The document wrote off 10 kg at the specified time;
  3. The report generated on the document date at 12 hours 31 minutes 36 seconds according to the Remaining table will show the balance of 30 kg;
  4. The same report on the table Remains and Turnovers for the same time will show a balance of 20 kg.

What is the reason for this behavior and how to avoid it?

The problem is that in the Remaining table, the period is specified as an open segment, that is, movements made at the time the report is generated are not taken into account, that is, the time is taken at the beginning of the second specified in the parameter. At the same time, for the table of Turnovers and for the table Remains and Turnovers, time boundaries are taken into account, that is, the time is taken at the end of the specified second.

There are several ways out of this situation:

  1. When using the Remains table, indicate a time point 1 second greater than the specified one;
  2. Use only the Remains and Turnovers table (not the most optimal option from a performance point of view);
  3. Use the concept of Boundary.

The last option can be represented by the code shown in Fig. 6.

In the first parameter of our object we indicate the date for which the report needs to be generated, the second parameter determines the type of border. Since it is important for us that movements on a given date are included in the selection, we must set this parameter to the “Including” position.

1C queries, as well as the built-in language of the 1C:Enterprise platform, have functions for working with dates. They help simplify date conversions in queries and avoid using a large number of parameters. Let's look at these functions.

How to set a date in 1C queries as a constant

DATETIME (Year, Month, Day, Hour, Minute, Second). Options Year, Month And Day are mandatory, others are not. If you do not specify the time, the system will set the start of the day.

Request. Text= "CHOOSE
DATETIME (2016,09,28,12,22,00)"; // Result: September 28, 2016 12:22:00

In addition, only numbers can be specified explicitly as parameters to this function. You cannot specify the values ​​of other functions as parameters. For example, this construction will not work:

Request. Text= "CHOOSE
DATETIME (Year (Source.Date),09,28,12,22,00)
From the Directory.Source AS Source";

How to get the beginning or end of a year, half a year, quarter, month, decade, week, day, hour, minute in 1C queries

For this, the following functions are used:

  • StartPeriod(Date, Period)
  • EndPeriod(Date, Period)

As a parameter date a value with type is passed Date of. Parameter Period .

Request. Text= "CHOOSE
StartPeriod(DATETIME (2016,09,28,12,22,00), DECADE)"; // Result: September 21, 2016 0:00:00

Request. Text= "CHOOSE
EndPeriod(DATETIME (2016,09,28,12,22,00), QUARTER)"; // Result: September 30, 2016 23:59:59

As you can see from the examples, you can use other nested functions in these functions.

How to get the year, day of the year, quarter, month, week, day of the week, day, hour, minute, second from a date in 1C queries

To do this, there are corresponding functions to which you need to pass the date as a parameter.

  • Function YEAR (Date)— returns the year number;
  • Function DAYYEAR (Date)— increases the number of the day in the year;
  • Function QUARTER (Date)— returns the quarter number;
  • Function MONTH (Date)— returns the month number;
  • Function WEEK (Date)— returns the number of the week in the year;
  • Function DAY OF THE WEEK (Date)— returns the number of the day in the week (Monday — 1, Sunday — 7);
  • Function DAY (Date)— returns the number of the day in the month;
  • Function HOUR (Date)— returns the hour;
  • Function MINUTE (Date)— returns minutes;
  • Function SECOND (Date)— returns seconds.

Request. Text= "CHOOSE
YEAR(DATETIME (2016,09,28,12,22,00))"; // Result: 2016

Request. Text= "CHOOSE
WEEK(DATETIME (2016,09,28,12,22,00))"; // Result: 40

Request. Text= "CHOOSE
WEEKDAY(DATETIME (2016,09,28,12,22,00))"; // Result: 3

Request. Text= "CHOOSE
DAYYEAR(DATETIME (2016,09,28,12,22,00))"; // Result: 272

Request. Text= "CHOOSE
DAY(DATETIME (2016,09,28,12,22,00))"; // Result: 28

How to add or subtract from a date a year, half-year, quarter, month, decade, week, day, hour, minute, second in 1C queries

To do this, use the function AddToDate(Date, Period, Value).

As a parameter date a value with type is passed Date of. Parameter Period can take one of the following values: SECOND, MINUTE, HOUR, DAY, WEEK, DECADE, MONTH, QUARTER, HALF-YEAR, YEAR.

Parameter Meaning shows the number of periods to be added. If the period needs to be subtracted, then the parameter Meaning must be negative.

Request. Text= "CHOOSE
AddToDate(DATETIME(2016, 9, 28, 12, 22, 0), HALF YEAR, 1)"; // Result: March 28, 2017 12:22:00

Request. Text= "CHOOSE
AddKDate(DATETIME(2016, 9, 28, 12, 22, 0), DECADE, -1)"; // Result: September 18, 2016 12:22:00

How to calculate the date difference in 1C queries

To do this, use the function DifferenceDate(Date1, Date2, Period).

Parameter Date1- the date to be subtracted.

Parameter Date2 — date from which to subtract.

Parameter Period can take one of the following values: SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER, YEAR. It shows in what units we want to get the result

Request. Text= "CHOOSE
DifferenceDate(DATETIME(2016, 9, 28, 12, 22, 0), DATETIME(2017, 9, 28, 12, 22, 0), QUARTER)"; // Result: 4

Request. Text= "CHOOSE
DifferenceDate(DATETIME(2016, 9, 28, 12, 22, 0), DATETIME(2017, 9, 28, 12, 22, 0), Second)"; // Result: 31,536,000

In all functions except the function DATE TIME, as a parameter date can be not only a specific date value (constant or query parameter), but also source table fields.



error: Content is protected!!