DateTime data type

Official Content
This documentation is valid for:

Stores date, time, seconds and milliseconds with this data type.

Syntax

DateTime

The following table illustrates the properties used to define the Data Type:

Value range property
Initial value property
Picture Properties Group
Precision property

 

The following table illustrates the conversion done by GeneXus according to the DBMS:

DBMS Native Type (Seconds Precision) Native Type (Milliseconds Precision)
Oracle Date TimeStamp
DB2 Universal Database Timestamp Timestamp
Informix DateTime DateTime(3)
SQL Server DateTime DateTime2
DB2 UDB for iSeries Timestamp Timestamp
PostgreSQL Timestamp Timestamp
SAP Hana SecondDate TimeStamp
MySQL DateTime Datetime(3)

 

Equivalencies Between Date and DateTime

Assignments
Assignments between Date and DateTime attributes are allowed according to the following rules:

Date = DateTime. The receiving field loses the value corresponding to the time.

DateTime = Date. The receiving field’s time value is 12:00:00 AM (00:00:00 in 24-hour format).

Comparisons
Comparisons between Date and DateTime fields in conditions are not allowed. To compare these two data types, you must convert one into other using assignments. For example, if &DT is a DateTime variable and &D is a Date variable you can use any of the following schemes to be able to compare them.
 

// Scheme 1, create a temporary DateTime variable and compare

&DT1 = &D  //Converts &D to DateTime and its value remains in &DT1
           //the time part is 12:00.000 AM
if &DT > &DT1
   …

// Scheme 2, create a temporary Date variable and compare

&D1 = &DT    //Converts &DT to Date and its value remains in &D1
             //the time part is lost
if &D > &D1
   …

 

Parameter Passing
Date and DateTime data types are not equivalent. Programs that expect a Date value cannot be called by programs passing a DateTime value and vice versa, otherwise, an error will occur at specification time.

Indices and Search
It is possible to create indices containing DateTime attributes, either as the only component or as part of the key. They can be either ascending or descending (as long as the DBMS allows it).

As far as the search is concerned, there are two things to consider:

The attribute will always be stored in the same way, regardless of the picture. That is, it will contain two parts, one for the date and one for the time.

It is not possible to search for those that 'have the same date' in a direct way. It will be necessary to implement, for example, the following code:

// Code to search for a DateTime attribute value within a given date.

&dtFrom = ymdhmstot(1995, 10, 28, 0, 0, 0)
&dtTo = ymdhmstot( 1995,10, 29, 0, 0, 0) – 1 // Next day minus 1 second
For each
    Where dtAttribute >= &dtFrom
    Where dtAttribute <= &dtTo
          …
EndFor

Reorganization

The following table discusses the possible data type changes of an attribute during the reorganization process.

Original Data type Data type to which it is converted
  Char1 Numeric2 Date Datetime
Char1 N/A Yes4. If val is used. Yes. If CtoD function is used. Yes. If CtoT function is used.
Numeric2 Yes. If Str function is used to convert. N/A N/A N/A
Date Yes3. If DtoC Function is used. N/A N/A Yes. If time 12:00:00.00 AM is assumed.
DateTime Yes3. If TtoC function is used with the default values. No Yes. The time part is lost. N/A

N/A. - Not applied.
Yes. - Conversion is allowed.
No. - Conversion is not allowed. The values are lost.
1 - Character, Varchar and Long Varchar types.
2 - Any numeric data type.
3 - It may happen that the receiving attribute is truncated to the right if it is not large enough.
4 - It may happen that the attribute is truncated to the left if it is not large enough.

Notes

  • Changes in the picture of the DateTime attribute do not imply database reorganization. This is because the way the attribute is stored is independent of the way of displaying it.
  • Changes in the precision may imply a database reorganization.
  • Empty values are invariable with respect to the Precision. Even though the minimum value for a Datetime2 in SQL Server is 0001-01-01, in GeneXus the empty and minimum is the same than the one for Datetime: 1753-01-01.

Static methods

DateTime.New(Year,Month,Day, [Hour],[Minute],[Second],[Millisecond])

Examples

&ExpirationDateTime = DateTime.New(2018,6,5,18,0,0,0)
If &ExpirationDateTime >= DateTime.New(2018,6,5,18,0,0,0)
   msg("The date and time is not allowed")
EndIf

 

See also

Date data type
Data types list

 


Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.