Dream 

How to specify an empty date in a request? 1c functions for working with dates in a request.

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. Document in specified time 10 kg were written off;
  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.

Quite often there is a need to work with variables of the “Date” type. In this article we will look at the basic techniques - passing the current date, checking for an empty value, an arbitrary date.

When writing queries, you often need to compare data with the current date. The built-in 1C language has the CurrentDate() function. It allows you to determine the current time and date on your computer. To perform operations with the current date, you must pass the value of this function as a parameter to the request.

Below is a query that selects all files attached to expense reports with a creation date up to now:

ExampleRequest = New Request;
Example Request.Text = "
|SELECT
| AdvanceReportAttachedFiles.Link
|FROM
| Directory.AdvanceReportAttachedFilesAS AdvanceReportAttachedFiles
|WHERE
| AdvanceReportAttachedFiles.Date< &ТекДата»;
Example Request.SetParameter("CurrentDate", CurrentDate());

Custom date

The function discussed above allows you to compare and, therefore, make a selection for any period. This method allows you to specify a strict selection in the request without using additional parameters.

Please note that when using this function in the example above, we only passed three numbers (year, month, day) as input parameters. The last three (hour, minute, second) are optional and, if absent, are replaced with “0”, that is, the beginning of the day.

This example will retrieve all files attached to expense reports up to the end of last year 2016. In this regard, we will indicate the hour, minute and second to compare the time point “December 31, 2016 23:59:59”.

CHOOSE
AdvanceReportAttachedFiles.Link
FROM
Directory.AdvanceReportAttachedFiles AS AdvanceReportAttachedFiles
WHERE
AdvanceReportAttachedFiles.Date< ДАТАВРЕМЯ(2016, 12, 31, 23, 59, 59)

Blank date

The easiest way to check a variable to see if it contains an empty date is to use a simple comparison. In this example, using a query, we will select all cash receipts to the account for which the incoming date is not filled in.

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.

The “Date” type in 1C is one of the 4 main data types along with number, string and Boolean. Dates are ubiquitous in configurations—it’s impossible to avoid working with this data type during development. Therefore, it is better to start writing queries already understanding how to process dates, what options there are for working with them, and how they are stored. Let's look at examples of all the nuances of writing queries with different dates.

Examples of working with dates in 1C queries

First of all, you need to get the date in the request in the required format - with or without time. There are several ways to accomplish this task:

  1. Pass via parameter. You can only get the current session date using this method;
  2. Get the date in the query from the selection field;
  3. Convert from numeric values ​​using the DATETIME() function.

The most common task when working with documents is checking for an empty date in a 1C request. In this case, it is easiest to compare a variable or field with an empty date, which is obtained using the DATETIME(1,1,1) function:

DATETIME (1, 1, 1)

Using a similar command, you can get an arbitrary date and time in a request. Moreover, they can be specified with an accuracy of up to a second by specifying 6 numbers as parameters. If only 3 numbers are used, then the hours, minutes and seconds will be set to 0 (beginning of the day). For example, we need to select documents for the first 10 days of January 2018 in the request:

SELECT Receipt to Current Account. Link AS Link FROM Document. Receipt to Current Account AS Receipt to Current Account WHERE Receipt to Current Account. Date BETWEEN DATETIME(2018, 1, 1, 0, 0, 0) AND DATETIME(2018, 1, 10, 23, 59, 59)

In a query in the built-in 1C language, you can not only select various fields and receive parameters. There are many functions that make it easier to format dates for a specific task. If you often work with dates in a query, then you should know these commands:

  • BEGINNING OF PERIOD. The parameters specify the date and time period in the context of which it is necessary to obtain the beginning of the date. Used to convert a date to a timeless format. To do this, you need to set the second parameter – “DAY”;
START OF PERIOD(,) START OF PERIOD(&Date, DAY) period>date>
  • END OF PERIOD. A similar command that returns the last date in terms of the units specified in the parameters;
  • ADDKDATE. Allows you to get a date that is greater by a specified number of specified time units. The function parameters include the date, time unit, and number;
ADDKDATE(,) ADDKDATE(&Date, DAY, 10) quantity>type>date>
  • DIFFERENCEDATE. Gets the difference between dates in the specified units;
DIFFERENCEDATE(,) DIFFERENCEDATE(&Date1, &Date2, DAY) type>date2>date1>
  • DAY OF THE WEEK. Returns the serial number of one of the days of the week.

By correctly using these functions, the developer can solve quite non-trivial problems. For example, getting the name of the day of the week of the current date in a request as a string:

SELECT WHENDAY OF THE WEEK(&CurrentDate) = 1 THEN "Monday" WHENDAYDAY(&CurrentDate) = 2 THEN "Tuesday" WHENDAY OF THE WEEK(&CurrentDate) = 3 THEN "Wednesday" WHENDAYDAY(&CurrentDate) = 4 THEN "Thursday" WHEN DAY WEEKS(&CurrentDate) = 5 THEN "Friday" WHENDAY OF THE WEEK(&CurrentDate) = 6 THEN "Saturday" ELSE "Sunday" END

Converting types in a 1C query from a number or string to a date is a labor-intensive task. You can get a date from numbers using the DATETIME function, from a string - by combining the SUBSTRING function and the SELECT WHEN THEN ELSE construction. Based on this, developers prefer to obtain the date from other types in the module and pass it to the request using a parameter. Unfortunately, this is not always feasible, so you have to change the date format in the request.

It is possible to specify the date in a 1C request as a parameter to obtain data from virtual register tables. This role can also use all of the above functions. But here it is important not to allow empty date in a 1C request could affect the final result of code execution, so a check must be done.

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 an 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 the specified date, returns the beginning of the period in which it belongs.
    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.



error: Content is protected!!