1s end of day in request. How to specify an empty date in a request? Convert to date

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 beginning 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 year, day of year, quarter, month, week, day of 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.

In this article we want to discuss everything with you 1C query language functions, and query language constructs. What is the difference between function and design? The function is called with parentheses and possible parameters in them, and the construct is written without parentheses. Undoubtedly all structures and functions of the 1C query language make the data acquisition process flexible and multifunctional. These functions and constructs apply to request fields, and some also apply to conditions.

1C Query Language Functions

Because a clear description 1C query language functions is much less common than descriptions of structures, we decided to start looking at functions. Now let's look at each one separately, describing its purpose, syntax and example of use, so:

1. Function DATE TIME- this function creates a constant field with the "Date" type.

Syntax: DATE TIME(<Год>,<Месяц>,<День>,<Час>,<Минута>,<Секунда>)

Usage example:

2. DATE DIFFERENCE function- returns the difference between two dates in one of the dimensions (year, month, day, hour, minute, second). The measurement is passed as a parameter.

Syntax: DIFFERENCEDATE(<Дата1>, <Дата2>, <Тип>)

Usage example:

Query.Text = "SELECT | DIFFERENCEDATE(DATETIME(2015, 4, 17), DATETIME(2015, 2, 1), DAY) | AS Qty.Days";

3. Function VALUE- sets a constant field with a predefined record from the database; you can also get an empty link of any type.

Syntax: VALUE(<Имя>)

Usage example:

Request.Text = "SELECT //predefined element | VALUE(Directory.Currencies.Dollar) AS Dollar, //empty link | VALUE(Document.Receipt of Goods and Services.EmptyLink) AS Receipt, //transfer value | VALUE(Transfer. Legal Individual. Individual) AS Individual, //predefined account | VALUE(Chart of Accounts. Self-supporting. Materials) AS Account_10" ;

4. SELECT function- we have before us an analogue of the IF construction, which is used in the code, only this one is used in 1C queries.

Syntax: CHOICE WHEN<Выражение>THEN<Выражение>OTHERWISE<Выражение>END

Usage example:

Request.Text = //if the amount is more than 7500, then there should be a discount of 300 rubles, //so if the condition is triggered then the function //returns Sum - 300 //otherwise the request will return simply Sum "SELECT | SELECT | WHEN TCReceipts.Amount > 7500 | THEN TCReceipts.Amount - 300 | ELSE TCReceipts.Amount | END AS AmountWithDiscount |FROM | Document.Receipt of GoodsServices.Goods AS TCReceipts";

5. EXPRESS function- allows you to express a constant field with a specific type.

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

Is there another option for using the EXPRESS function in fields of mixed types, where do they occur? The simplest example is the “Registrar” for any register. So why might we need to qualify the type in the registrar? Let's consider the situation when we select the "Number" field from the registrar, from which table will the number be selected? The correct answer of all! Therefore, for our query to work quickly, we should specify an explicit type using the EXPRESS function

Usage example:

Query.Text = "SELECT | EXPRESS(Nomenclature.Comment AS Line(300)) AS Comment, | EXPRESS(Nomenclature.Sum AS Number(15,2)) AS Sum |FROM | Directory.Nomenclature AS Nomenclature";

6. ISNULL function(alternative spelling ISNULL) - if the field is of type NULL, then it is replaced with the second parameter of the function.

Syntax: ISNULL(<Поле>, <ПодставляемоеЗначение>)

Usage example:

Also note that it is advisable to ALWAYS replace the NULL type with some value, because comparison with type NULL always returns FALSE even if you compare NULL with NULL. Most often, NULL values are formed as a result of joining tables (all types of joins except internal ones).

Query.Text = //Select the entire item and its balances //if there is no balance in some item, then there will be a field //NULL which will be replaced with the value 0 "SELECT | No. Link, | ISNULL(ProductsInStockRemains.InStockRemaining, 0) AS Remainder | FROM | Directory.Nomenclature AS No. | LEFT CONNECTION Register Accumulations. GoodsInWarehouses. Remainings AS GoodsInWarehousesRemains | ON (GoodsInWarehousesRemains. Nomenclature = No. Link)";

7. REPRESENTATION function- allows you to get a representation of the request field.

Syntax: PERFORMANCE(<НаименованиеПоля>)

Usage example:

Query.Text = "SELECT | REPRESENTATION(FreeRemainingRemains.Nomenclature) AS Nomenclature, | REPRESENTATION(FreeRemainingRemaining.Warehouse) AS Warehouse, | FreeRemainingRemaining.InStockRemaining |FROM |Accumulation Register.FreeRemaining.Remaining AS FreeRemainingRemaining";

Constructs in the 1C query language

We discussed with you above 1C query language functions, now it's time to consider constructs in the 1C query language, they are no less important and useful, let’s get started.

1. Construction LINK- is a logical operator for checking a reference type. Most often encountered when checking a field of a complex type against a specific type. Syntax: LINK<Имя таблицы>

Usage example:

Request.Text = //if the recorder value type is document Receipt, //then the query will return "Receipt of goods", otherwise "Sales of goods" "SELECT | SELECT | WHEN Remainings.Registrar LINK Document.Receipt of Goods and Services | THEN ""Receipt"" | ELSE ""Consumption"" | END AS Type of Movement | FROM | Accumulation Register. Remaining Products in Warehouses AS Remains" ;

2. Design BETWEEN- this operator checks whether the value is within the specified range.

Syntax: BETWEEN<Выражение>AND<Выражение>

Usage example:

3. Construction B and B HIERARCHY- check whether the value is in the transferred list (arrays, tables of values, etc. can be transferred as a list). The IN HIERARCHY operator allows you to view the hierarchy (an example of using the Chart of Accounts).

Syntax: IN(<СписокЗначений>), IN HIERARCHY(<СписокЗначений>)

Usage example:

4. Design SIMILAR- This function allows us to compare a string with a string pattern.

Syntax: LIKE "<ТекстШаблона>"

Row pattern options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

[...] - any single character or sequence of characters listed inside square brackets. The enumeration can specify ranges, for example a-z, meaning an arbitrary character included in the range, including the ends of the range.

[^...] - any single character or sequence of characters listed inside square brackets except those listed after the negation sign.

Usage example:

5. Design ALLOWED- this operator allows you to select only those records from the database for which the caller has read permission. These rights are configured at the record level (RLS).

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

Request.Text = "SELECT ALLOWED | Counterparties. Link | FROM | Directory. Counterparties AS Counterparties";

6. Design VARIOUS- allows you to select records in which there are no duplicate records.

Syntax: VARIOUS is written after the keyword SELECT

Usage example:

Request.Text = //selects records to which the reader has rights "SELECT VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties" ;

Also, the VARIOUS construction can be used with the ALLOWED operator and other operators.

Usage example:

Request.Text = //selects various records to which the reader has rights "SELECT ALLOWED VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties";

7. Design FIRST- selects the number of records specified in the parameter from the query result.

Syntax: FIRST<число>

Usage example:

Request.Text = //select the first 4 CCD numbers from the directory "SELECT FIRST 4 | CCD Numbers. Link | FROM | Directory. CCD Numbers AS CCD Numbers";

8. Design FOR CHANGE- allows you to lock a table, works only in transactions (relevant only for automatic locks).

Syntax: FOR CHANGE<НаименованиеТаблицы>

Usage example:

9. Design ORDER BY- organizes data by a specific field. If the field is a link, then when setting the flag AUTO ORDER Sorting will occur by link representation; if the flag is turned off, then links are sorted by the seniority of the link address in memory.

Syntax: SORT BY<НаименованиеПоля>AUTO ORDER

Usage example:

10. Design GROUP BY- used to group query strings by specific fields. Numeric fields must be used with any aggregate function.

Syntax: GROUP BY<НаименованиеПоля1>, .... , <НаименованиеПоляN>

Usage example:

11. Design HAVING- allows you to apply an aggregate function to a data selection condition, similar to the WHERE construction.

Syntax: HAVING<агрегатная функция с условием>

Usage example:

12. Construction INDEX BY- used for indexing the query field. A query with indexing takes longer to complete, but speeds up searching through indexed fields. Can only be used in virtual tables.

Syntax: INDEX BY<Поле1, ... , ПолеN>

Usage example:

13. Design WHERE- allows you to impose a condition on any selection fields. The result will include only records that satisfy the condition.

Syntax: WHERE<Условие1 ОператорЛогСоединения УсловиеN>

Usage example:

Query.Text = //all records with CompensationRemaining are selected<>0 and //AmountForCalcCompRemaining > 100 "SELECT | CompensationRPORemains.Counterparty, |CompensationRPORemains.Child, | CompensationRPORemains.CompensationRemaining, | CompensationRPORemains.AmountForCalcCompRemains |Place DataTz |FROM | Accumulation Register.CompensationRP.Remains AS CompensationRPRemains | WHERE |CompensationRPORemaining.CompensationRemaining<>0 | And CompensationRPORemains.AmountForCalcCompRemaining> 100" ;

14. Design RESULTS... GENERAL- used to calculate totals; the design specifies the fields by which totals will be calculated and aggregate functions applied to the total fields. When using totals for each field following the TOTAL construction, data is grouped. There is an optional GENERAL construct; its use also provides additional grouping. You will see an example of the request result below.

Syntax: RESULTS<АгрегатнаяФункция1, ... , АгрегатнаяФункцияN>BY<ОБЩИЕ> <Поле1, ... , ПолеN>

Usage example:

The figure outlines the groupings that were formed during the execution of the request, the top one refers to the GENERAL section, and the second to the Counterparty AgreementAgreement Type field.

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.

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:

Pass via parameter. You can only get the current session date using this method;
Get the date in the query from the selection field;
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 indicate 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:

SELECTING WHENDAY OF THE WEEK(&CurrentDate) = 1 THEN "Monday" WHENDAYDAY(&CurrentDate) = 2 THEN "Tuesday" WHENDAYDAY(&CurrentDate) = 3 THEN "Wednesday" WHENDAYDAY(&CurrentDate) = 4 THEN "Thursday" WHEN DAY DELHI(&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 an empty date in a 1C request to affect the final result of executing the code, so a check must be done.

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 am not trying to give a complete description of the query language, but want to dwell only 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...

Popular

Parthenogenesis - technique of the immaculate conception

« The concept of parthenogenesis During fertilization, the sperm releases the egg from its dormant state and it begins to develop. But in nature there are cases... »

The Tale of the Three Brothers Who gave the three brothers Harry Potter accessories

« Once upon a time there lived Three Brothers of a Wizard, and one day they needed to cross a river; they couldn’t swim across it, it was too wide and deep. And brothers... »

De Broglie hypothesized that particles of matter

« O.S.Ageeva, T.N.Stroganova, K.S.Chemezova ELEMENTS OF QUANTUM MECHANICS AND SOLID STATE PHYSICS Tyumen. 2009 UDC 537(075):621.38 Ageeva O.S.,... »