Comparison with empty date in 1c request

All documents existing in 1C configurations, and, therefore, almost all registers must have at least one variable 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 date and time boundary.

It is to these questions that we will try to answer in our article.

What is a date and how to determine it

Since most of the management decisions and accounting does not require time accuracy exceeding 1 second, the developers of the 1C platform decided that this value would be the maximum minimum in the date format. Thus, each variable describing the time of an event in the program must contain:

  • The year the event occurred;
  • The month of this event;
  • Day.

Optional: 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.

Date formats existing in the world have a significant difference:

  1. In Russia, we are used to putting the day first, then comes the month of the event, and at the end - the year;
  2. US residents start the date with a month;
  3. Czechs, Poles and Slovenes write the period in the format “Year - Month - Day”.

It is the last format that the 1C platform uses.

Convert to date

In order to get a parameter with the Date type from several values ​​or from a string, you must use the code shown in Fig. one

As you can see from the above figure, you can determine the date both with the help of one line, and with the division of 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, month, day, hours and seconds - must be two characters long, including leading zeros.

The countdown in the program starts from the beginning of the day on January 1, 0001. For the above code, this value can be determined in one of two ways (Fig. 2).

Rice. 2

In the second line, we omitted the hours, minutes and seconds of the event, which in no way affected the performance of our code.

Features of using the date in 1C requests

For most of the data types used by the 1C platform, there are predefined void values. For numbers, this is 0, for links, you can define the EmptyReference () value, for a date, it is customary to consider the start date as an empty value, it is with it that you need to compare the details of the corresponding type when setting the query parameters.

It is important to understand that even if no numbers are indicated in the value of the form attribute of the type in question, that is, the window looks like (Fig. 3), this does not mean that nothing is specified in it, the 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 given query text, we omitted the leading zeros for the year, month and day, and also did not indicate the hours, minutes and seconds, and the program, as they say, ate this assumption.

Date and time boundary

Another interesting fact about the relationship between queries and dates is the use of the "point in time" concept when referring to various database tables.

Accuracy "up to a millisecond" indicated in the technical documentation when describing the primitive Date type is most clearly manifested when selecting records from virtual tables of the accumulation register: if the accumulation register, in addition to the Turnovers table, has tables Remains and Remains and Turnovers, then the selection for them, carried out for a certain time may give different results.

To understand how and why this happens, consider a simple example:

  1. Before the document of sale was carried out at 12 hours 31 minutes 36 seconds, the balances according to the Sugar nomenclature were 30 kg;
  2. The document at the indicated time was written off 10 kg;
  3. The report generated as of the date of the document at 12 hours 31 minutes 36 seconds according to the Balances table will show the remainder of 30 kg;
  4. The same report on the table Residuals and Turnovers at the same time will show a residual of 20 kg.

What is the reason for this behavior and how to avoid it?

The problem is that in the Remains table, the period is set by 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 Turns table and for the Remaining AND Turnover table, the 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 Residuals table, indicate a time point 1 second greater than the specified one;
  2. Use only the table Remains and Turnovers (not the most optimal option in terms of performance);
  3. Use the concept of Border.

The last option can be represented by the code shown in Fig. 6.

In the first parameter of our object, we indicate the date on which it is necessary to generate the report, the second parameter determines the type of border. Since it is important for us that the movements for a given date are included in the sample, we must set this parameter to the "Including" position.

Very often in 1C requests you have to work with dates. Especially when the query is being built for metadata objects that contain periodic information. As a rule, these are registers (information, accumulation, calculation, accounting). Let's consider the most frequently used functions of the 1C query language for working with dates. We will build examples based on the information register EmployeesOrganizations ZUP configuration revision 2.5.

  • DATE TIME

    Allows you to get the date in the request (with or without time) by specifying the year, month, day, hour, minute, second.
    Syntax:
    DATE TIME (Year, Month, Day, Hour, Minute, Second)
    Usually the hour, minute and second are not specified. Let's give a mini example. Enter the following text in the Query Console:

    SELECT DATE TIME (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 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, the empty date looks like - 0001.01.01. Thus, to get an empty date in the request, it is enough to specify DATE TIME (1, 1, 1)... As an example, let's select from the information register EmployeesOrganizations entries that are not filled Period of Completion:

    SELECT Employees of Organizations. Period, Employees of Organizations. Employee, Employees of Organizations. Position, Employees of Organizations. Subdivision of an Organization FROM the Register of Information. Employees of Organizations AS Employees of Organizations WHERE Employees of Organizations.

  • BEGINNING OF PERIOD

    Returns the beginning of the period in which it belongs for the specified date.
    Syntax:
    PERIOD START (Date, PeriodType)
    PeriodType can take the following values: MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, DECADE, SEMI-ANNUAL
    In the query console, enter:

    SELECT START OF PERIOD (DATE TIME (2016, 1, 15), MONTH)

    The request will return - 01.01.2016
    Now for an example. As you know the frequency at the register EmployeesOrganizations one day. Let's compose a query in which the date of the beginning of the month will be displayed instead of the valid period of records.

    CHOOSE THE BEGINNING OF THE PERIOD (Employees of Organizations. Period, MONTH) AS The Beginning of the Month, Employees of Organizations. Employee, Employees of Organizations. Position, Employees of Organizations. A Subdivision of an Organization.

  • 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 type of period. We will not consider in detail. Let's restrict ourselves to a mini example.
    Inquiry:

    SELECT END OF PERIOD (DATE TIME (2016, 1, 15), MONTH)

    Returns 1/31/2016 23:59:59
    As you can see, the value is returned accurate to the second.

  • ADDDATE

    Adds the specified number of time slots to the date.
    Syntax:
    ADD TO DATE (Date, PeriodType, Count)
    PeriodType accepts the same values ​​as for the function BEGINNING OF PERIOD
    Let's take a February date as an example:

    SELECT ADD TO DATE (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 that of the original. It's very convenient that you don't have to think about the number of days in months.
    The number can be negative. Then the interval is counted in the opposite direction.

  • DIFFERENCE

    Calculates the difference between two dates in the specified units.
    Syntax:
    DATE DIFFERENCE (Start Date, End Date, PeriodType)
    The period type can take the following values: SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER, YEAR
    For instance:

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

    returns 15

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

When working with 1C dates, the typical order of the 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 in the localized format (day.month.year hours: minutes: seconds), but only completely.

For instance:
// 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 ways
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 dates 1C - Empty date 1C

To check the 1C date for fullness - it is compared with the "empty date". If there is a date type attribute in the reference book / document, if the user has not filled in this field, then its value will also be “empty date”.

"Empty date" is 01.01.0001 00:00:00.

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

Working with dates 1C - Date in the details (reference books, documents, etc.)

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

  • Date only (time is then always 00:00:00)
  • Time only (date is then always 01.01.0001)
  • Date and time

Getting the date

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

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

In order to get the server date (the date set in the operating system of the server computer), a common module is usually created in the configuration with the "Server" checkbox checked in the properties, and the function is created in it
// the function is located in a common module, for example, named ServerFunctions
// in the properties of the common module, the "Server" checkbox is checked and the "Client" checkbox is not set
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 functions of the modules, it is indicated where it will be executed:

Start and end of the day

For the date "10/30/2012":

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

It is used in reports and queries that require data for a period - day, month, year.

So, 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 it does include one second of the last day of the month).

Working with 1C dates - Comparing dates

Date contains date and time. When comparing dates (excluding time), they are usually brought to the beginning of the day (month, year).

For instance:
Date1 = Date ("10/30/2012 12:00:00");
If StartDay (Date1) = StartDay (DocumentRef.Date) Then
Report ("The document was entered by the specified date");
EndIf;

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

Working with dates 1C - Changing the date

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

For instance:
DayStart = CurrentDate () - DayStart (CurrentDate ());
Report ("Since the beginning of the day has passed" + String (From the beginning of the day) + "seconds");
Report ("Since the beginning of the day has passed" + String (From the beginning of the day / 60) + "minutes");
Report ("Since the beginning of the day has passed" + String (From the beginning of the day / 60/60) + "hours");

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

BeginningPreviousDay = BeginningDay (BeginningThisDay - 1); // remove the second - doing "yesterday" and take the beginning of the day from "yesterday"

StartPreviousDay = StartThisDay - 24 * 60 * 60; // another way - we subtract 24 hours - 24 (hours) * 60 (minutes turned out) * 60 (seconds)

Working with dates 1C - Moment of time

A point in time is an extended date representation applicable to documents (and accordingly 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 when filtering in queries.

A moment in time can be obtained from a document in the following ways:
// method 1
DocumentMomentTime = DocumentRef.Timepoint ();

You can also compare a point in time with a date / time:
Moment of TimeStatus = New Moment of Time (Start of Day (CurrentDate ()));
If DocumentRef.MomentTime (). Compare (TimeTimeStandard) = -1 Then
Report ("The document was introduced earlier than today");
EndIf;
// If the document was entered by today's date at 00:00:00, then it was entered anyway - today

Working with dates 1C - Date formatting

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

A NULL value is returned when there is simply no props. The type in this case will also be NULL. For example, you are joining two tables through a left join. In the case when no values ​​are found in the right table for the left table, NULL will be returned.

The check for this value can be carried out using the constructions "IS NULL" and "". In the first case, it returns True or False. 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 specified segment.

SELECT
ContactPartners.Link
FROM
Directory.Partners' Contact Persons AS PARTNER'S CONTACT PERSONS
INTERNAL JOINT Directory.SegmentsPartners AS SegmentsPartners
Software Partner Contacts.Owner = Partner Segments.Parent
WHERE
Partner Segments.Ref IS NULL

Empty date

Validation for an empty date is performed by comparing it with the DATE TIME (1, 1, 1, 0, 0, 0) construction. An example of use is given below:

Empty link in 1C request

In the case when the returned attribute has a reference type, for example, it is an element of some dictionary, document, etc., the following construction is used: VALUE (Directory.ReferenceName.EmptyRef).

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 line

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

The query below will select all partners with a blank name.

At some point, 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, it is often necessary 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, it is necessary to 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;
ExampleRequest.Text = "
| CHOOSE
| Advance ReportAttached Files.Link
| FROM
| Reference.AvailableReportAttachedFilesASA AdvanceReportAttachedFiles
| WHERE
| Advance Report Attached Files.Date< &ТекДата»;
ExampleRequest.SetParameter ("CurrentDate", CurrentDate ());

Custom date

The above function 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.

Note that 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.

For this example, you will receive all files attached to expense reports up to the end of last 2016. In this regard, we will indicate the hour, minute and second in order to compare with the moment in time "December 31, 2016 at 23:59:59".

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

Empty date

It is easiest to check a variable to see if it contains an empty date with a simple comparison. In this example, using a query, we will select all receipts of funds to a bank account, for which the incoming date is not filled.



Recommended to read