Transform Templates - Datetime

This topic discusses application of the Transform pane with Tables on a datetime field in a table.   See the Transform pane and Transform Templates topics for use and for links to lists of templates for other data types for tables and use of the Transform pane with other components, such as Drawings and Images.

Tables, Datetime Type

Clear Time

Clear, that is, set to 0:00:00, the time part of the target datetime field, using the date specified.

 

 

We start with a table of datetime values in the Temp_datetime field, all on May 25, 2020, but with different times.  

 

 

We choose Temp_datetime as the target field, Clear Time as the transform and we choose Temp_datetime as the source field for the Date.

 

 

The result is the same date part of the datetime values, taken from the same Temp_datetime field, with the time part being cleared, that is, set to 0:00:00.  

 

 

If we like, we can choose the value option for the Date field and specify a date using any of the allowed forms for date literals set forth in the SQL Constants and Literals topic.

 

 

The result is a table with 0:00:00 for the time parts and November 26, 2020, for the date part.    This transform uses the DateTimeClearTime  SQL function.

 

Compose Date

Given a year number, a month number, and a day number, create a datetime value and put it into the target field.

 

 

Choosing Calendar as the target field, we choose a value for the Year and enter 2020.  We use whatever happens to be the value of the mfd_id field for each record for the Month and the Day value.

 

 

The preview shows how the transform generates datetime values all having 2020 for the year, while taking the month and day from the mfd_id value for each record

 

 

We now enter 5 for the Month and 25 for the Day.

 

The preview shows all dates being the same, 25 May 2020.   This transform uses the DateTimeMake  SQL function.

 

Copy

Copy the source field into the target field.  A literal datetime value can be specified by choosing Expression and then entering a datetime SQL style, bracketed by # # characters.

 

 

Preview: Copy Calendar into Temp_datetime.

Date Difference

Appears when the target field is a numeric data type.  Given a source field that is a datetime field, and given datetimes to use as the beginning and the end of the comparison range, save into the target field the difference in days between the beginning and end date.  The difference can be positive, if the beginning date is before the end date, or negative, if the beginning date is after the end date.  

 

The difference will have a decimal fractional part if the times in the two datetimes are not the same.  To capture fractional parts, use a target field that is a floating point data type, and not an integer type.

 

 

We start with a table that has a datetime field and a numeric field.    We have scrolled the table to show the last two records, which have dates for the first day and the last day in the year 2020.  

 

 

Choosing the numeric field as the target, the Date Difference transform appears in the list of templates.  For the Begin field we choose the datetime field.  We choose values for the End field and enter November 26, 2020 as the End datetime.  Not specifying any time portion of the datetime, that means we have specified a datetime of November 26, 2020 at 0:00:00.  

 

 

The preview shows how the transform fills the Numbers field with the number of days difference between the datetime of the record and November 26, 2020.   We see that the start of Thanksgiving (November 26) in 2020 is almost 330 days from the second to last datetime, about 25 minutes into the first day of the new year in 2020.

 

From the last record, we can see that Thanksgiving is about 35 days before 6:53 in the morning on the last day of that year.

 

 

To specify an exact time on the End datetime we can use a different format for the datetime literal, as described in the SQL Constants and Literals topic.  We specify the date of November 26, 2020 using exactly the same time as in the second to last record of the example table.

 

 

In that case, the preview shows how for the second to last record there are exactly 330 days from the datetime of 1/1/2020 0:25:13 to our End datetime of 11/26/2020 0:25:13.

 

This transform uses the DateTimeDifferenceDays  SQL function.

Date Shift

Appears when the target field is a datetime data type.  Given a source field that is a datetime field, adds the number of days specified by the Shift days parameter to create a new date.   Positive values for the Shift days parameter add days to create a later date, and negative values subtract days to create an earlier date.  Values for Shift days can have decimal fraction parts, to shift by part of a day.  For example, a value of 10.5 will shift by ten days and 12 hours (half of a day).

 

 

We start with a table that has datetime values.   Note that the date of the last record is  December 25, 2016.

 

 

Choosing the datetime field as the target, the Date Shift transform appears in the list of templates.  For the Date field we choose the datetime field.  We enter 10 for the number of Shift days.  

 

 

The preview shows dates with ten days added to each date.   Dates are automatically shifted into the next month and next year as required.  Note that the times for the last two records are after noon, while the times for the preceding records are before noon.

 

 

Suppose we enter 10.5 for the number of Shift days.   That will add 10 days and 12 hours (half a day) to the datetimes in the table.

 

 

Note from the preview that dates have been shifted by the number of days, with times also shifted forward by 12 hours as well.   The last two records, for example, have been pushed into the early morning of the next day, compared to just shifting dates by ten days.

 

 This transform uses the DateTimeAddDays  SQL function.

Date Week

Appears when the target field is a numeric data type.  Given a source field that is a datetime field, reports the week number for the datetime, counting the week containing January 1 as week 1.  The Week start parameter specifies what day a week starts, the default being Sunday.  The week number for the last day of the year depends on the days of the week for that date and for January 1, and on the Week start parameter we choose.

 

 

We start with a table that has a datetime field and an integer field.    We have scrolled the table to show the last two records, which have dates for the first day and the last day in the year 2020.  January 1 in the year 2020 falls on a Wednesday, while December 31 in year 2020 falls on a Thursday.

 

 

Choosing the integer field as the target, the Date Week transform appears in the list of templates.  For the Date field we choose the datetime field.  We leave the Week start parameter at the default value of Sunday.

 

 

The preview shows how for each date the transform reports the week number of the week in the datetime value.   Weeks are counted starting with the week that contains January 1 as week 1.  We can see that September 25, 2012, fell within week 39.   

 

For the last two records, January 1 in the year 2020 falls on a Wednesday, while December 31 in year 2020 falls on a Thursday.   In 2020, since January 1 fell on a Wednesday, the 52nd week of the year ended on Wednesday, December 30, 2020.   The last day, Thursday, December 31, 2020, thus is in the 53rd week of the year.

 

 

We can change the Week start parameter to Thursday to see how that alters the week numbers reported.

 

 

Looking at the last two records, January 1, a Wednesday, is still in week 1, but the very next day, Thursday, is already week 2 since we have specified a Week start day of Thursday.   Counting forward to the end of the year, week 53 ends on December 30, a Wednesday, and December 31, a Thursday, falls into week 54.

 

 This transform uses the DateTimeWeek  SQL function.

Date Week Day

Appears when the target field is a numeric data type.  Given a source field that is a datetime field, reports the day of the week number for that date, counting from the start of the week.  The Week start parameter specifies what day a week starts, the default being Sunday.   By default a date that falls on a Sunday will report a day of the week number of 1, a date that falls on Monday will report a day of the week number of 2, and so on to a date that falls on Saturday, which will report a day of the week number of 7.

 

 

We start with a table that has a datetime field and an integer field.    We have scrolled the table to show the last two records, which have dates for the first day and the last day in the year 2020.  January 1 in the year 2020 falls on a Wednesday, while December 31 in year 2020 falls on a Thursday.

 

 

Choosing the integer field as the target, the Date Week transform appears in the list of templates.  For the Date field we choose the datetime field.  We leave the Week start parameter at the default value of Sunday.

 

 

The preview shows how for each date the transform reports the sequential day number within the week for each datetime value.  

 

For the last two records, January 1 in the year 2020 falls on a Wednesday, while December 31 in year 2020 falls on a Thursday.  In that case, January 1, a Wednesday, falls on day 4 of the week, while December 31, a Thursday, falls on day 5 of the week.   We can see from the preview that August 25, 2013, fell on a Sunday, day 1.

 

 

We can change the Week start parameter to Thursday to see how that alters the day of the week numbers reported.

 

il_trans_date_week_day_datetime05.png

 

Looking at the last two records, January 1, a Wednesday,now is day of the week 7 but the very next day, Thursday, is now day of the week.,  since weeks now start on a Thursday.

 

This transform uses the DateTimeWeekDay  SQL function.

Date Year Day

Appears when the target field is a numeric data type.  Given a source field that is a datetime field, reports the day number for the datetime, counting January 1 as day 1 and December 31 as day 365.

 

 

We start with a table that has a datetime field and an integer field.  

 

 

Choosing the integer field as the target, the Date Year Day transform appears in the list of templates.  For the Date field we choose the datetime field.

 

 

The preview shows how for each date the transform reports the day number of the day in the datetime value.  For example, January 25 is the 25th day of the year and February 25 is the 56th day of the year.   This transform uses the DateTimeYearDay  SQL function.

 

Date Year Leap

Appears when the target field is a boolean data type.  Given a source field that is a datetime field, reports true or false whether the year in the datetime is a leap year.

 

 

We start with a table that has a datetime field and a boolean field.  

 

 

Choosing the boolean field as the target, the Date Year Leap transform appears in the list of templates.  For the Date field we choose the datetime field.

 

 

The preview shows how for each date the transform reports, true or false, whether it is a leap year.   This transform uses the DateTimeYearLeap  SQL function.

 

Limit

Limit the result at both the low end and also the high end, to no less than the limiting value in the At least field and also to no more than the limiting value in the At most field.  Save the Value field into the result,  unless that is below the At least field, in which case the At least field value is saved into the result, or unless that exceeds the At most field, in which case the At most field value is saved into the result.  Another way of looking at this is to take the Value field if it falls between the At least and At most fields, inclusive.   Yet another way is to consider simultaneously the At least field as the floor and the At most field the ceiling for what is taken from the Value field.

 

A literal datetime value can be specified in any parameter box by choosing Expression and then entering a datetime SQL style, bracketed by # # characters.

 

 

Preview: Put in Temp_datetime the Limit of Calendar as the Value, and the date of #12/15/2016 12:00:00# specified as the At least value, and the date of #12/18/2016 12:00:00# specified as the At most value.

In records where the Calendar date is between the specified At least and At most dates, the Calendar date is used.  Otherwise, the limiting low date specified in the At least box is used for dates that sort before the Calendar date and the limiting high date specified in the At most box is used for dates that sort after the Calendar date.

Limit High

Limit the result at the high end, to no more than the limiting value in the At most field.  Save the Value field into the result, unless that exceeds the At most field, in which case the At most field value is saved into the result.  Another way of looking at this is to take the minimum of the Value field or the At most field.  Yet another way is to consider the At most field the ceiling for what is taken from the Value field.

 

A literal datetime value can be specified in either parameter box by choosing Expression and then entering a datetime SQL style, bracketed by # # characters.

 

 

Preview: Put in Temp_datetime the Limit High of Calendar as the Value and the date of #12/15/2016 12:00:00# specified as the At most value.

In records where the Calendar date is before the high limit, that is, below the ceiling, specified in the At most box, the Calendar date is used.  Otherwise, the limiting high date specified in the At most box is used.

Limit Low

Limit the result at the low end, to no less than the limiting value in the At least field.  Save the Value field into the result, unless that is below the At least field, in which case the At least field value is saved into the result.  Another way of looking at this is to take the maximum of the Value field or the At least field.  Yet another way is to consider the At lease field the floor for what is taken from the Value field.

 

A literal datetime value can be specified in either parameter box by choosing Expression and then entering a datetime SQL style, bracketed by # # characters.

 

 

Preview: Put in Temp_datetime the Limit Low of Calendar as the Value and the date of #12/15/2016 12:00:00# specified as the At least value.

In records where the Calendar date is after the low limit, that is, above the floor, specified in the At least box, the Calendar date is used.  Otherwise, the limiting low date specified in the At least box is used.

Parse Base64 String

Extracts a datetime value encoded into a base64-encoded string and places it in the target datetime field.

 

Parse Hex String

Extracts a datetime value encoded into a hex-encoded string and places it in the target datetime field.

 

See Also

Transform

 

Transform Pane

 

Transform Options

 

Transform Templates

 

Transform Templates - Drawings

 

Transform Templates - Images

 

Transform Templates - Binary

 

Transform Templates - Boolean

 

Transform Templates - Geom

 

Transform Templates - Numeric

 

Transform Templates - Text

 

Transform Templates - Tile

 

Transform: Center and Centroids

 

Transform: Escape Templates