%SUBDT
Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times.
This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT.
Valid unit should be passed for the type of the value passed. Below are the valid values for each type.
Date – *DAYS, *MONTHS, *YEARSTime – *HOURS, *MINUTES, *SECONDSTimestamp – *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS
Syntax:
%SUBDT(value : unit { : digits { : decpos} })
Value and Unit are the mandatory arguments. Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp.
We can either pass the full form for the unit or use the short form. Below is the mapping of Full form to short form.
*YEARS – *Y*MONTHS – *M*DAYS – *D*HOURS – *H*MINUTES – *MN*SECONDS – *S*MSECONDS – *MS
Let’s have a look at couple of simple examples to see how this works.
E.g.:
Extract Date, Month and Year from a Date field.
We are using two different date formats *ISO & *DMY which shows different number of digits for year (*ISO – 4 digits and *DMY – 2 digits). However, %SUBDT would always return the full year (4 digits) irrespective of the format of the date passed. Line – 17: %SUBDT with *ISO date format. Line – 22: %SUBDT with *DMY date format. We don’t necessarily pass the date in a variable. Lines – 17 & 22: We are passing the date variables to extract the year. Line – 27: We are passing %DATE() to extract the month from the current date. Line – 32: We are passing the required date to extract the day from the passed date.
Let’s have a look at another example to extract Hours, Minutes and Seconds from a Time.
Line – 17: We are using *HOURS full form for value instead of short form *H. Using any of these two would return the same result. Either a variable with time, %TIME() or a time literal can be passed to %SUBDT. Line – 15: A time variable is being passed to extract hours. Line – 20: %TIME() is being passed to extract minutes. Line – 25: Specific time can be passed directly by using time literal ‘t’. We are extracting seconds from the time passed.
Let’s have a look at the final example to extract the milli seconds by passing the Timestamp. We will also explore the two optional parameters in this example.
Extracting Years, Months, Days, Hours and Minutes from Timestamp is same as extracting from Date and Time. Milliseconds can be extracted from Timestamp and optional parameters can be used on seconds to specify how many decimal positions are to be extracted for milliseconds.
In the above example,
Line – 14: Extracting milliseconds from timestamp. Line – 19: Extracting seconds from timestamp along with milliseconds by specifying optional parameters digits and number of decimal positions. We are passing digits (total) ‘6’ and decimal positions ‘4’. %SUBDT would only return milliseconds up to 4 digits along with seconds. wSeconds variable has been defined with 6 decimal positions, so the last two decimal positions would be stored as ’00’. If the third parameter (digits) is specified, Fourth parameter (decimal positions) must be specified. And, Fourth parameter should always be 2 less than third parameter (2 digits for seconds).If optional parameters aren’t specified, no milliseconds would be returned.
If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.
