SPL: Date, Time and Datetime Handling
SPL offers a wealth of functions for date, time and datetime handling, including calculating the current time/date, extracting a component from a date/time/datetime, combining components to generate a date/time/datetime, format conversion, precision tuning, getting a related date, calculating the time interval, calculating the relative date, equivalence comparison, workdayrelated calculations, evenly spaced time series handling, etc. These functions can be divided into two categories – basic handling and computations.
1. Basic handling
1.1 Calculating the current time/date
Task 
Code 
Return value 
Return the current time, accurate to millisecond 
now() 

Return the current date 
now@d() 

Return the current time 
now@t() 

Return the current time, accurate to minute 
now@m() 

Return the current time, accurate to second 
now@s() 
1.2 Extracting component
Extract the year, month, date, hour, minute, second and week from date, time or datetime objects or strings.
In the following table, dt is a datetime object, such as datetime("20180815 16:07:58:327","yyyyMMdd HH🇲🇲ss:SSS").
Task 
Code 
Return value 
Get the year 
year(dt) 
2018 
Get the month 
month(dt) 
8 
Get the year and month 
month@y(dt) 
201808 
Get the day 
day(dt) 
15 
Get the day of the week for the current date 
day@w(dt) 
4(Sunday is 1, Monday is 2) 
Get the hour 
hour(dt) 
16 
Get the minute 
minute(dt) 
7 
Get the second 
second(dt) 
58 
Get the millisecond 
millisecond(dt) 
327 
Get the date part in a datetime 
date(dt) 
date("20180815") 
Get the time part in a datetime 
time(dt) 
time("16:07:58") 
Get the time part in a datetime , accurate to minute 
time@m(dt) 
time("16:07:00") 
1.3 Combining components
Generate a date, time or datetime object using a year value, a month value, an hour value, a minute value, a second value, a long integer, or a datetime string.
In the following table, dt is a datetime object, such as datetime("20180815 16:07:58:327","yyyyMMdd HH🇲🇲ss:SSS").
Task 
Code 
Return value 
Generate a date using the year, month and day components 
date(2018,8,15) 

Generate a time using the hour, minute and second components 
time(16,7,58) 

Generate a datetime using the year, month, day, hour, minute, second components 
datetime(2018,8,15,16,7,58) 

Generate a datetime using the date and time components 
datetime(date("20180815"), time("16:07:58")) 
1.4 Conversion
In the following table, d is a date object, such as date("20180815");
t is a time object, such as time("16:07:58:327","HH🇲🇲ss:SSS");
dt is datetime object, such as datetime("20180815 16:07:58:327","yyyyMMdd HH🇲🇲ss:SSS").
Task 
Code 
Return value 
Adjust datetime precision, accurate to day 
datetime(dt) 
20180815 00:00:00 
Adjust datetime precision, accurate to minute 
datetime@m(dt) 
20180815 16:07:00 
Adjust datetime precision, accurate to second 
datetime@s(dt) 
20180815 16:07:58 
Convert a string to date of default format 
date("20180815") 

Convert a string to date according to the specified format 
date("08/15/2018","MM/dd/yyyy") 

Convert a string to date according to the specified format 
date("201808","yyyyMM") 
20180801 
Convert a string to time of default format 
time("16:07:58") 

Convert a string to time according to the specified format 
time("4:07 PM","h:mm a") 
16:07:00 
Convert a string to datetime according to the specified format 
datetime("20180815 16:07:58") 

Convert a string to datetime according to the specified format 
datetime("08/15/2018 4:07 PM", "MM/dd/yyyy h:mm a") 

Convert to default date format string 
string(d) 
20180815 
Convert to the specified date format string 
string(dt,"MM/dd/yyyy") 
08/15/2018 
Convert to the specified date format string 
string(dt,"yyyyMM") 
201808 
Convert to default time format string 
string(t) 
16:07:58 
Convert to the specified time format string 
string(dt,"h:mm a") 
4:07 PM 
Convert to default datetime format string 
string(dt) 
20180815 16:07:58 
Convert to the specified datetime format string 
string(dt,"MM/dd/yyyy h:mm a") 
08/15/2018 4:07 PM 
Convert to a long integer to datetime 
datetime(321656865654) 
19800312 05:07:45 
Convert to a long integer (represented as the number of milliseconds since January 1, 1970, 00:00:00 GMT) 
long(dt) 
1534320478000 
2. Computations
2.1 Calculating a related date
In the following table, dt is a date object, such as date("20200416").
Task 
Code 
Return value 
Get the first day (Sunday) of the week given in the specified date 
pdate@w(dt) 
20200412 
Get the first day of the month given in the specified date 
pdate@m(dt) 
20200401 
Get the first day of the quarter given in the specified date 
pdate@q(dt) 
20200401 
Get the first day of the year given in the specified date 
pdate@y(dt) 
20200101 
Get the last day (Saturday) of the week given in the specified date 
pdate@we(dt) 
20200418 
Get the last day of the month given in the specified date 
pdate@me(dt) 
20200430 
Get the last day of the quarter given in the specified date 
pdate@qe(dt) 
20200630 
Get the last day of the year given in the specified date 
pdate@ye(dt) 
20201231 
Count the days of the month given in the specified date 
days(dt) 
30 
Count the days of the quarter given in the specified date 
days@q(dt) 
91 
Count the days of the year given in the specified date 
days@y(dt) 
366 
2.2 Calculating the time interval
Calculate the time interval between two time points.
In the following table, dt1 and dt2 are datetime("20080808 20:00:00") and datetime("20180528 10:27:15") respectively.
Task 
Code 
Return value 
Get the number of full years between the given datetime and the current datetime 
age(dt1) 
13 
Get the number of full years between two given datetimes 
age(dt1,dt2) 
9 
Get the number of years between the years that two given datetimes belong to 
age@y(dt1,dt2) 
10 
Get the number of years between the months that two given datetimes belong to 
age@m(dt1,dt2) 
9 
Get the difference between two given datetimes in terms of years 
interval@y(dt1,dt2) 
10 
Get the difference between two given datetimes in terms of quarters 
interval@q(dt1,dt2) 
40 
Get the difference between two given datetimes in terms of months 
interval@m(dt1,dt2) 
117 
Get the difference between two given datetimes in terms of seconds 
interval@s(dt1,dt2) 
309277635 
Get the difference between two given datetimes in terms of milliseconds 
interval@ms(dt1,dt2) 
309277635000 
Get the number of Sundays between two given datetimes 
interval@7(dt1,dt2) 
512 
Get the number of Mondays between two given datetimes 
interval@1(dt1,dt2) 
512 
Get the difference between two given datetimes in terms of days and return an integer 
interval(dt1,dt2) 
3580 
Get the difference between two given datetimes in terms of days and return an integer 
dt2dt1 
3580 
Get the difference between two given datetimes in terms of days and return a real number 
interval@r(dt1,dt2) 
3579.6022569444444 
2.3 Calculating a relative datetime
elapse(dt,k)
The function calculates a new datetime when a specified number of days, years, quarters, months, seconds or milliseconds k is added to or subtracted from a given datetime dt. The new date is in the future when k is a positive number and in the past when it is a negative number. If the original datetime is in the last day a month, the function, by default, will adjust the result datetime to the last day of the current month.
In the following table, dt is datetime("20170228 10:27:15"), which is in the last day of February.
Task 
Code 
Return value 
Get the datetime three days after the given datetime 
dt+3 
20170303 10:27:15 
Get the datetime three days after the given datetime 
elapse(A2,3) 
20170303 10:27:15 
Get the datetime three years after the given datetime (adjust the result to the last day of the current month) 
elapse@y(A2,3) 
20200229 10:27:15 
Get the datetime three years after the given datetime (do not adjust the result to the last day of the current month) 
elapse@ye(A2,3) 
20200228 10:27:15 
Get the datetime three quarters after the given datetime (adjust the result to the last day of the current month) 
elapse@q(A2,3) 
20171130 10:27:15 
Get the datetime three quarters after the given datetime (do not adjust the result to the last day of the current month) 
elapse@qe(A2,3) 
20171128 10:27:15 
Get the datetime four months before the given datetime (adjust the result to the last day of the current month) 
elapse@m(A2,4) 
20161031 10:27:15 
Get the datetime four months before the given datetime (do not adjust the result to the last day of the current month) 
elapse@me(A2,4) 
20161028 10:27:15 
Get the datetime 100 seconds after the given datetime 
elapse@s(A2,100) 
20170228 10:28:55 
Get the datetime 368 milliseconds after the given datetime 
elapse@ms(A2,368) 
20170228 10:27:15:368 
2.4 Equivalence comparison
Task 
Code 
Return value 
Compare whether two dates are equal 
deq("19881208","19881207") 
false 
Compare whether the years in the two given dates are equal 
deq@y(date("19881108"),date("19880912")) 
true 
Compare whether the months in the two given dates are equal 
deq@m(date("19881108"),date("19880912")) 
false 
Compare whether the quarters in the two given dates are equal 
deq@q(date("19881208"),date("19881012")) 
true 
Compare whether the tenday periods in the two given dates are equal 
deq@t(date("19881008"),date("19881012")) 
false 
Compare whether the weeks in the two given dates are equal 
deq@w(date("19881005"),date("19881008")) 
true 
2.5 Workdayrelated calculations
workday(dt,k,h) Calculates the workday k days after the given date dt.
workdays(dt1,dt2,h) Returns a sequence of workdays between date dt1 and date dt2.
h is a sequence of holidays or nonholidays, whose member (members) is (are) treated as a holiday (holidays) if they are not weekends, and as a nonholiday (nonholidays) if they are weekends.
Task 
Code 
Return value 
Get the workday two workdays of the given date 
workday(date("20200429"),2,[date("20200501")]) 
20200504 
20200501is Friday, which is a public holiday 

Get the workday two workdays of the given date 
workday(date("20200925"),2,[date("20200927")]) 
20200928 
20200927 is a Sunday, which is a workday in lieu 

Get a sequence of workdays between two given dates 
=workdays(date("20200924"),date("20200929"),[date("20200927")]) 
[20200924,20200925,20200927,20200928,20200929] 
2.6 Evenlyspaced time series
periods(s,e,i)
s and e are time variables; i is an integer. The function returns a sequence of dates/times that are a certain time period (i) apart between two given time points s (inclusive) and e (inclusive). The default unit of i is day, and its default value is 1. We can use @x option in the function to exclude the endpoints.
Task 
Code 
Return value 
Get a sequence of dates that are three days apart between two given time points 
periods("20180925","20181006",3) 
[20180925,20180928, 
Get a sequence of dates that are three years apart between two given time points (Adjust to full years apart) 
periods@y("20100925","20181006",3) 
[20100925,20130101, 
Get a sequence of dates that are three years apart between two given time points (Do not adjust to full years apart) 
periods@yo("20100925","20181006",3) 
[20100925,20130925, 
Get a sequence of dates that are three quarters apart between two given time points (Adjust to full quarters apart) 
periods@q("20100925","20120506",3) 
[20100925,20110401, 
Get a sequence of dates that are three quarters apart between two given time points (Do not adjust to full quarters apart) 
periods@qo("20100925","20120506",3) 
[20100925,20110625, 
Get a sequence of dates that are three months apart between two given time points (Adjust to full months apart) 
periods@m("20100925","20110406",3) 
[20100925,20101201, 
Get a sequence of dates that are three months apart between two given time points (Do not adjust to full months apart) 
periods@mo("20100925","20110406",3) 
[20100925,20101225, 
Get a sequence of dates that are two tenday periods apart between two given time points 
periods@t("20100925","20101106",2) 
[20100925,20101011, 
Get a sequence of times that are three seconds apart between two given time points 
periods@s("08:25:30","08:25:39",3) 
[08:25:30,08:25:33, 
range(s,e,k:n)
Both s and e are time variables. The function divides the time interval between s and e evenly into n segments, and returns the time range of the k^{th} segment. The result’s degree of precision is determined by the data type of s and e. Accurate to day when they are date type, and to second when they are datetime type.
Task 
Code 
Return value 
Get the segment points after the interval between two given dates are trisected 
range(date("20180809"),date("20200220"),3) 
[20180809,20190212, 
Get the range of the 2^{nd} segment after the interval between two given dates are trisected 
range(date("20180809"),date("20200220"),2:3) 
[20190212,20190818] 
Get the range of the 2^{nd} segment after the interval between two given datetimes are trisected 
range(datetime("20180101 10:20:30"), datetime("20200101 10:00:00"),2:3) 
[20180901 18:13:40, 
Find more date/time/datetime operations in date(), time(), and datetime() functions in SPL Function Reference.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL