1c comparison of dates in the request. How to specify an empty date in a query? Convert to date

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

When writing queries, it is often necessary to compare data with the current date. The 1C built-in 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, the value of this function must be passed to the request as a parameter.

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

RequestExample = New Request;
ExampleRequest.Text ="
|CHOOSE
| Advance ReportAttachedFiles.Link
| FROM
| Directory.Advance ReportAttachedFilesAS AdvanceReportAttachedFiles
|WHERE
| Advance ReportAttachedFiles.Date< &ТекДата»;
Query Example.SetParameter("CurrentDate", CurrentDate());

Arbitrary 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 query without using additional parameters.

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

As part of this example, all files attached to advance reports up to the end of last year 2016 will be received. In this regard, we will specify the hour, minute and second to compare with the point in time "December 31, 2016 23:59:59".

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

empty date

The easiest way to check a variable for whether it contains an empty date is by a simple comparison. In this example, using a query, we will select all cash receipts to the account that do not have an incoming date filled in.

  43
 NULL - missing values. Not to be confused with null! NULL is not a number, not equal to a space, null reference, undefined. NULL is a type-forming value, i.e. there is a NULL type and a single value of this type. NULL...   26
 To form and execute queries to database tables in the 1C platform, a special object of the Request programming language is used. This object is created by calling the New Request construct. Convenient request...   18
 The article provides useful tricks when working with 1C v.8.2 queries, as well as information that is not so well known about the query language. I don't try to give Full description query language, but I want to focus only on ...   13
 LIKE - An operator to check if a string is similar to a pattern. Analogue of LIKE in SQL. The LIKE operator allows you to compare the value of the expression specified to the left of it with the template string specified to the right. Expression value...

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 is 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 are the possibilities for working with them, 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 desired format - with or without time. There are several ways to accomplish this task:

1. Pass via parameter. This method is the only way to get the current session date;
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 query. In this case, it is easiest to compare the variable or field with an empty date, which is obtained using the DATETIME(1,1,1) function:

With a similar command, you can get an arbitrary date and time in the request. At the same time, they can be specified up to a second by specifying 6 numbers as parameters. If only 3 numbers are used, then 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 a query:

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

• BEGINNING OF PERIOD. As parameters, specify the date and time interval in the context of which it is necessary to get the beginning of the date. Used to convert a date to a timeless format. To do this, you must set the second parameter - "DAY";

• ENDPERIOD. A similar command that returns the last date in the context of the units specified in the parameters;
• ADDDATE. Allows you to get a date greater by a given number of specified time units. Date, time unit and number are specified as function parameters;

• DATE DIFFERENCE Gets the difference between dates in the specified units;

• DAY OF THE WEEK. Returns the ordinal number of one of the days of the week.

Properly applying these functions, the developer can solve quite non-trivial tasks. For example, getting the name of the day of the week of the current date in the query as a string:

Converting types in a 1C query from a number or string to a date is a laborious task. From numbers, you can get the date 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 get 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 the 1C request as a parameter for obtaining data from virtual register tables. In this role, you can also use all of the above functions. But here it is important not to let empty date in a 1C request could affect the final result of the code execution, so it is necessary to do a check.

Very often in 1C queries you have to work with dates. Especially when the request is built to metadata objects that contain periodic information. As a rule, these are registers (information, accumulation, calculation, accounting). Consider 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 the date in the query (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 specified. Let's take a mini example. Enter the following text in the query console:

  SELECT DATETIME(2016, 1, 1)

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

  SELECT Employees of Organizations.Period, Employees of Organizations.Employee, Employees of Organizations.Position, Employees of Organizations.Department of Organizations FROM Register of Information.Employees of Organizations AS Employees of Organizations WHERE Employees of Organizations.Completion Period = DATETIME(1, 1, 1)

• BEGINNING OF PERIOD

  For specified date returns the beginning of the period it belongs to.
  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 frequency of the register EmployeesOrganizations one day. Let's create a query in which instead of the actual period of records, the date of the beginning of the month will be displayed.

  CHOOSE THE BEGINNING OF THE PERIOD (Employees of Organizations.Period, MONTH) AS Beginning of the Month, Employees of Organizations.Employee, Employees of Organizations.Position, Employees of Organizations.Department of the Organization FROM the Register of Information.Employees of Organizations AS Employees of Organizations

• ENDPERIOD

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

  SELECT ENDPERIOD(DATETIME(2016, 1, 15), MONTH)

  Returns 31.01.2016 23:59:59
  As you can see, the value is returned accurate to the second.

• ADDDATE

  Adds the specified number of timeslots to the date.
  Syntax:
  ADDDATE(Date, PeriodType, Count)
  PeriodType takes the same values ​​as for the function BEGINNING OF PERIOD
  Let's take February as an example:

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

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

• DATE DIFFERENCE

  Calculates the difference between two dates in the specified units.
  Syntax:
  DATEDIFF(StartDate, EndDate, PeriodType)
  The period type can take the following values: SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER, YEAR
  For example:

  SELECT DATE DIFFERENCE(DATETIME(2016, 2, 15), DATETIME(2016, 3, 1), DAY)

  returns 15

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