Check empty date in 1c request. Working with Null Values ​​in a Query

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 it is 0, for links you can define the value EmptyLink(), for a date the empty value is usually considered to be the starting date, and 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 the value of a form attribute of the type in question does not contain any numbers, 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 about 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 sampling from them is carried out for a 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.

When working with queries, any programmer has to interact with empty values ​​in one way or another. What do we mean by empty value?

A null value is either no value or the default value for the data type. With primitive types, everything is quite simple: the default value is some initial value that serves as a starting point.

Types of Null Values

Let's look at the types of empty values ​​that may be encountered or required in a query.

• For the Number type, the empty value is zero – 0.
• For the String type – an empty string – “”.
• For the Date type – January 1st of the first year – 01/01/0001 00:00:00. It is from this date that time is counted in 1C.*
• For the Boolean type, the default value is technically False, but logically both values ​​of the type are padded. Therefore, deciding whether an empty value is False or not is based on the logic of a specific algorithm.

*Be careful, outside 1C there are different date counting systems with different starting points.

The missing value fully corresponds only to the type Null. This type contains only one value, which indicates no value.

Similar type Undefined also contains only one value, but Undefined does not mean the absence of data, but only the impossibility of determining the default value for the type. Undefined is the default value for composite types, including those not explicitly defined. For example, a value in a new row of a table of values ​​in a column for which the type is not explicitly defined.

Co reference types there is much less uncertainty. All reference types provide a null value. An empty value is the same reference indicating the data type, but without a unique identifier for the specific value. Thanks to this, we can treat an empty link as if it were a regular one and apply all the methods provided by the platform to it, working with it as if it were a full-fledged value.

Working with Null Values ​​in a Query

Whether you need to explicitly enter a null value into a query result or compare existing values ​​to a null value, you need to know how to describe null values ​​in your query.

Types Number, String, Boolean are described in the request as in the built-in language:

Undefined, being essentially a primitive type, is described similarly:

Empty reference values ​​are a little more difficult to define. All reference objects have a predefined service value of EmptyRef. Thanks to this, it is possible to select an empty link in a single way - through the Value function:

The possibilities for working with Null values ​​are somewhat richer. Like other primitive types, Null is described in the same way as in the built-in language. In addition, there is a special operator Is Null and a function IsNull.

• The Is Null operator allows you to create a logical expression that compares the selected value with the Null value.
• The IsNull function returns the first argument if it is not Null, and the second argument otherwise.

Expressions that define empty values ​​can be used in any query sections that support expressions. For example, you can add an empty link to the Select section or a Null check to the Condition.

Practical examples

Using the Value function

Using the Is Null operator

Null on left or full join

Checking for Null

The example demonstrates a common practical situation when, with a left join, there is no match for the first table in the second. In this case, all fields of the second table will be Null.

Handling Null Values

Modification of the previous query to demonstrate a common technique for obtaining some default values ​​to replace missing ones. In this example, using the IsNull function, the missing remainder value is replaced with a logically correct 0.

In this article, we looked at various types of empty values ​​and their properties, studied ways to define different types of empty values ​​in queries, and in the practical part we were convinced of the ease of application of the material discussed.

This article will look at ways to check for an empty value depending on the type of attribute being checked, including an empty link.

The NULL value is returned in the case when the attribute simply does not exist. The type in this case will also be NULL. For example, you join two tables using a left join. In the event that no value is found in the right table for the left table, NULL will be returned.

Checking for this value can be done using the “IS NULL” and “ ” constructions. In the first case, True or False is returned. In the second case, you can immediately set a different value in the case when NULL is returned.

The 1C 8.3 request below will return a list of contact persons for those partners who do not have a segment specified.

CHOOSE
Contact PersonsPartners.Link
FROM
Directory.Contact Persons of Partners AS Contact Persons of Partners
INTERNAL JOIN Directory.Partner Segments AS Partner Segments
Software Contact Persons of Partners.Owner = Segments of Partners.Parent
WHERE
Partner Segments. Link IS NULL

Blank date

The value is checked for an empty date by comparing it with the DATETIME(1, 1, 1, 0, 0, 0) construction. An example usage is given below:

Empty link in 1C request

In the case when the returned attribute is of a reference type, for example, it is an element of a directory, document, etc., the following construction is used: VALUE(Directory.DirectoryName.EmptyLink).

In the example below, the query selects all partners that do not have a business region specified.

To check for “ValueFilled” you need to do the opposite condition:

Partners.BusinessRegion<>VALUE(Directory.BusinessRegions.EmptyLink)

Empty string

To check string types, a comparison is made with another sample. In this case - "".

The query below will select all partners with an empty name.

When working with 1C dates, the typical order of date parts is year, month, day, hour, minutes, seconds. In this case, hours, minutes, seconds can be skipped.

When creating a date from a string (“cast to date”), you can specify it in a localized format (day.month.year hours:minutes:seconds), but only in full.

For example:
//Working with 1C dates - convert a date to 1C from parts - year, month, day (plus optional time)
Date = Date(2012,10,30); //no time
Date = Date(2012,10,30,12,00,00); //over time

//Working with 1C dates - convert a date to 1C from a string, different methods
Date = Date("20121030"); //year, month, day
Date = Date("10/30/2012 12:00:00"); //localized format, only in full

//Working with 1C dates - specifying the date value without casting, directly
Date = "20121030"; //no time
Date = "20121030120000"; //over time

Working with 1C dates - Blank date 1C

To check the 1C date for completeness, it is compared with the “empty date”. If there is an attribute with the date type in the reference book/document, if the user does not fill in this field, then its value will also be “empty date”.

“Empty date” is 01/01/0001 00:00:00.

For example:
EmptyDate = "00010101000000";
If RequiredDate = "00010101000000" Then
Report("You did not fill in a very necessary date");
endIf;

Working with dates 1C - Date in details (directories, documents, etc.)

When specifying the type of attribute, you can specify to use:

• Only the date (the time is then always 00:00:00)
• Time only (the date is then always 01/01/0001)
• Date and time

Getting the date

To obtain the date and time, use the 1C CurrentDate() function.

The place where this function is called is very important - on the client or on the server. For more information, see the topic “Performance Mode/Performance”. It often happens that the time on client machines is slightly different, so they try to use the server time everywhere - even if it is set incorrectly on the server, then at least all clients will have the same incorrect time.

In order to get the server date (the date set in the operating system of the server computer), usually a common module is created in the configuration with the “Server” checkbox checked in the properties, and a function is created in it
//the function is located in a common module, for example with the name Server Functions
//in the properties of the common module the “Server” checkbox is checked and the “Client” checkbox is not checked
Function GetServerDate() Export
Return CurrentDate();
EndFunction

//calling this function for use from another module looks like this
DocumentObject.Date = ServerFunctions.GetServerDate(); //ModuleName.FunctionName()

Also in the thin client, directly next to the module functions, it is indicated where it will be executed:

Start and end of the day

For the date “10/30/2012”:

• the start date of the day looks like this “10/30/2012 00:00:00”
• the end of day date looks like this: “10/30/2012 23:59:59”

Used in reports and queries that require obtaining data for a period - day, month, year.

For example, the period from “01/01/2012 00:00:00” to “01/31/2012 00:00:00” is incorrect because it does not include one day of the month (but does include one second of the last day of the month).

Working with 1C dates - Comparing dates

Date contains the date and time. When comparing dates (without taking into account time), they are usually reduced to the beginning of the day (month, year).

For example:
Date1 = Date("10/30/2012 12:00:00");
If StartDay(Date1) = StartDay(DocumentLink.Date) Then
Notify("The document was entered on the specified date");
endIf;

Just in case, an example of comparing dates in a period:
If DocumentLink.Date >= Beginning of Month(CurrentDate()) and
DocumentLink.Date

Working with dates 1C - Changing the date

The date is the number of seconds. If we want to not only find out whether one date is greater than another, but also how much greater, then we get the difference in seconds.

For example:
FromBeginning Of Day = CurrentDate() – Beginning Of Day(CurrentDate());
Report("Since the beginning of the day " + Line(From the Beginning of the Day) + " seconds have passed");
Report("Since the beginning of the day " + Line(From the beginning of the day/60) + " minutes have passed");
Report("Since the beginning of the day " + Line(From the Beginning of Day/60/60) + " hours have passed");

We can also change the date; when changing, we add or subtract the number of seconds:
StartThisDay = StartDay(CurrentDate());

Start ofPreviousDay = Start ofDay(StartofThisDay – 1); //remove the second - doing “yesterday” and take the beginning of the day from “yesterday”

Start of Previous Day = Start of This Day – 24*60*60; //another method - subtract 24 hours - 24 (hours) * 60 (minutes) * 60 (seconds)

Working with dates 1C - Moment in time

A point in time is an extended representation of a date that applies to documents (and thus registers).

It is required to compare the time of documents if the date and time of the documents are the same. Accordingly, it can be used for selection in queries.

A point in time can be obtained from a document in the following ways:
//method 1
DocumentTimePoint = DocumentLink.TimeTime();

You can also compare a point in time with a date/time:
TimePointReference = New TimePoint(Start of Day(CurrentDate()));
If DocumentRef.TimePoint().Compare(TimeTimeReference) = -1 Then
Notify("The document was entered earlier than today");
endIf;
//If a document is entered with today's date at 00:00:00, then it is still entered - today

Working with dates 1C - Date formatting