datetime(X;Y)

Returns the date+time value corresponding to a given date and time.

Syntax

datetime(X;Y)

Input

Argument Type Description
X
  • integer
  • big integer
 A scalar value or the name of a column containing date-related values
Valid values can be of the form:
  • date
Y
  • integer
  • big integer
 A scalar value or the name of a column containing time-related values
Valid values can be of the form:
  • time

For more information, see Dates and time.

Note: Support for columns of type big integer available as of version 11.25.

Return Value

Returns the decimal value (in date+time form) corresponding to the date and time values given as input.

If the input values are not in one of the supported formats listed above, the function returns an erroneous or illogical output.

Example

The following example uses datetime(X;Y) to create a date+time value from two columns in the pub.demo.weather.wunderground.observed_hourly table: date and time.

Note: Because the pub.demo.weather.wunderground.observed_hourly table is so large, the calculations are only done for one particular zip code.
<base table="pub.demo.weather.wunderground.observed_hourly" 
 label="Example: Elapsed time since login"/>
<sel value="(zipcode='10017')"/>
<willbe name="observed_date_time" value="datetime(date;time)" 
 label="Observed Date/Time" format="type:datehms24"/>
<colord cols="date,time,observed_date_time"/>

Example: Elapsed time since login

The following example uses datetime(X;Y) to create a date+time value from two columns in the pub.demo.weather.wunderground.observed_hourly table: date and time. It then determines the login date and time of the user (using the logindatetime(X) function) and calculates the number of days, hours, minutes, and seconds that have elapsed since the time the user logged in.

Note: Because the pub.demo.weather.wunderground.observed_hourly table is so large, the calculations are only done for one particular zip code.
<base table="pub.demo.weather.wunderground.observed_hourly" 
 label="Example: Elapsed time since login"/>
<sel value="(zipcode='10017')"/>
<willbe name="observed_date_time" value="datetime(date;time)" 
 label="Observed Date/Time" format="type:datehms24"/>
<willbe name="login_date_time" value="logindatetime(-5)" 
 label="Login Date/Time" format="type:datehms24"/>
<willbe name="login_date" value="logindate(-5)" 
 label="Login Date" format="type:date"/>
<colord cols="zipcode,date,time,observed_date_time,login_date_time"/>
<willbe name="delta" value="login_date_time-observed_date_time" format="dec:2"/>
<willbe name="elapsed_days" value="if(delta < 1; 0; days(date;login_date))" 
 label="Elapsed Days"/>
<willbe name="elapsed_hours" value="time(delta)" 
 label="Elapsed Time" format="type:hms24"/>