1s checking the date is empty. Convert to date

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.

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); //with 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"; //with 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

Tools for identifying empty links, dates, or strings. Let's look at how to check for an empty link, date or line in a 1C request.

You can define null data by using special operators.

Information from the 1C database is requested (and then received) using a tool such as query language (Query). They are written in Latin and Cyrillic. And one of the keyword commands (operators) when receiving information is the command word SELECT (English) in combination with some constructions. Here are examples of constructing requested tasks that interest us on the topic:

Checking for NULL content

This task is performed by the SELECT operator in combination with the “IS NULL” construction:

SELECT
OrdersRemains.Customer,
OrdersRemaining.QuantityRemaining
FROM
RegisterAccumulations.Orders.Remains AS OrdersRemains
WHERE
OrdersRemaining.QuantityRemaining IS NULL

Date control

An empty date in a 1C request is entered using the construction “DATETIME(1, 1, 1, 0, 0, 0)”:

SELECT
InvoiceIncomingSupplier.Link.
Invoice Incoming Supplier. Date of Arrival
FROM
Document. Supplier Invoice AS Supplier Invoice
WHERE
InvoiceIncomingSupplier.IncomingDate = DATETIME(1, 1, 1, 0, 0, 0)

Reference check

Non-existent intra-reference values (for charts of accounts, directories, etc.) are checked using the “VALUE(Directory.Name…EmptyLink)” construction:

String check

Empty lines are detected by using a space in quotation marks "":

SELECT
Buyers.Link
FROM
Directory. Buyers AS Buyers
WHERE
Buyers.Code = ""

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:

In Russia, we are accustomed to putting the day first, then the month of the event, and at the end the year;
US residents start the date with the month;
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 regarding 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:

Before the sales document was carried out at 12 hours 31 minutes 36 seconds, the balances according to the Sugar nomenclature were 30 kg;
The document wrote off 10 kg at the specified time;
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;
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:

When using the Remains table, indicate a time point 1 second greater than the specified one;
Use only the Remains and Turnovers table (not the most optimal option from a performance point of view);
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.

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.

1s checking the date is empty. Convert to date

Custom date

Blank date

Checking for NULL content

Date control

Reference check

String check

More about broken and regular links

In the 1C-7.7 system

In the 1C-8.x system

Where do they come from

What is a date and how to determine it

Convert to date

Features of using date in 1C queries

Date and time boundary

DATE TIME

BEGINNING OF PERIOD

END OF PERIOD

ADDKDATE

DIFFERENCEDATE