Skip to content

Date and Time functions

Date and time functions are used to transform and convert date and time values. They are based on a date-time serial number that equals the number of days since December 30, 1899. The integer value represents the day and the fractional value represents the time of the day.

The engine uses the numerical value of the parameter, so a number is valid as a parameter also when it is not formatted as a date or a time. If the parameter does not correspond to numerical value, for example, because it is a string, then the engineattempts to interpret the string according to the date and time environment variables.

If the time format used in the parameter does not correspond to the one set in the environment variables, the engine will not be able to make a correct interpretation. To resolve this, either change the settings or use an interpretation function.

In the examples for each function, the default time and date formats hh:mm:ss and YYYY-MM-DD (ISO 8601) are assumed.

Note

When processing a timestamp with a date or time function, the engine ignores any daylight savings time parameters unless the date or time function includes a geographic position. For example, ConvertToLocalTime(filetime('Time.qvd'), 'Paris') would use daylight savings time parameters while ConvertToLocalTime(filetime('Time.qvd'), 'GMT-01:00') would not use daylight savings time parameters.

addmonths

This function returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

AddMonths(startdate, n, [ , mode])

Return data type: dual

Argument Description
startdate The start date as a time stamp, for example '2012-10-12'.
n Number of months as a positive or negative integer.
mode mode specifies if the month is added relative to the beginning of the month or relative to the end of the month. If the input date is the 28th or above and mode is set to 1, the function will return a date which is the same distance from the end of the month as the input date. Default mode is 0.
Example Result
addmonths ('2003-01-29',3) returns '2003-04-29'
addmonths ('2003-01-29',3,0) returns '2003-04-29'
addmonths ('2003-01-29',3,1) returns '2003-04-28'
addmonths ('2003-01-29',1,0) returns '2003-02-28'
addmonths ('2003-01-29',1,1) returns '2003-02-26'
addmonths ('2003-02-28',1,0) returns '2003-03-28'
addmonths ('2003-02-28',1,1) returns '2003-03-31'

addyears

This function returns the date occurring n years after startdate or, if n is negative, the date occurring n years before startdate.

AddYears(startdate, n)

Return data type: dual

Argument Description
startdate The start date as a time stamp, for example '2012-10-12'.
n Number of years as a positive or negative integer.
Example Result
addyears ('2010-01-29',3) returns '2013-01-29'
addyears ('2010-01-29',-1) returns '2009-01-29'

age

The age function returns the age at the time of timestamp (in completed years) of somebody born on date_of_birth.

age(timestamp, date_of_birth)

Return data type: numeric

Argument Description
timestamp The timestamp,or expression resolving to a timestamp, up to which to calculate the completed number of years.
date_of_birth Date of birth of the person whose age is being calculated. Can be an expression.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
age('25/01/2014', '29/10/2012') Returns 1.
age('29/10/2014', '29/10/2012') Returns 2.
Employees:
LOAD * INLINE [
Member|DateOfBirth
John|28/03/1989
Linda|10/12/1990
Steve|5/2/1992
Birg|31/3/1993
Raj|19/5/1994
Prita|15/9/1994
Su|11/12/1994
Goran|2/3/1995
Sunny|14/5/1996
Ajoa|13/6/1996
Daphne|7/7/1998
Biffy|4/8/2000
] (delimiter is |);
AgeTable:
Load *,
age('20/08/2015', DateOfBirth) As Age
Resident Employees;
Drop table Employees;
The resulting table shows the returned values of age for each of the records in the table.
Member DateOfBirth Age
John 28/03/1989 26
Linda 10/12/1990 24
Steve 5/2/1992 23
Birg 31/3/1993 22
Raj 19/5/1994 21
Prita 15/9/1994 20
Su 11/12/1994 20
Goran 2/3/1995 20
Sunny 14/5/1996 19
Ajoa 13/6/1996 19
Daphne 7/7/1998 17
Biffy 4/8/2000 15

converttolocaltime

Converts a UTC or GMT timestamp to local time as a dual value. The place can be any of a number of cities, places and time zones around the world.

ConvertToLocalTime(timestamp [, place [, ignore_dst=false]])

Return data type: dual

Argument Description
timestamp The timestamp,or expression resolving to a timestamp, to convert.
place

A place or timezone from the table of valid places and timezones below. Alternatively, you can useGMT or UTC to define the local time. The following values and time offset ranges are valid:

  • GMT
  • GMT-12:00 - GMT-01:00
  • GMT+01:00 - GMT+14:00
  • UTC
  • UTC-12:00 - UTC-01:00
  • UTC+01:00 - UTC+14:00
ignore_dst Set to True if you want to ignore DST (daylight saving time).

Note

You can only use standard time offsets. It's not possible to use an arbitrary time offset, for example, GMT-04:27.

The resulting time is adjusted for daylight-saving time, unless ignore_dst is set to True.

Valid places and time zones:

  • Abu Dhabi
  • Adelaide
  • Alaska
  • Amsterdam
  • Arizona
  • Astana
  • Athens
  • Atlantic Time (Canada)
  • Auckland
  • Azores
  • Baghdad
  • Baku
  • Bangkok
  • Beijing
  • Belgrade
  • Berlin
  • Bern
  • Bogota
  • Brasilia
  • Bratislava
  • Brisbane
  • Brussels
  • Bucharest
  • Budapest
  • Buenos Aires
  • Cairo
  • Canberra
  • Cape Verde Is.
  • Caracas
  • Casablanca
  • Central America
  • Central Time (US & Canada)
  • Chennai
  • Chihuahua
  • Chongqing
  • Copenhagen
  • Darwin
  • Dhaka
  • Eastern Time (US & Canada)
  • Edinburgh
  • Ekaterinburg
  • Fiji
  • Georgetown
  • Greenland
  • Greenwich Mean Time : Dublin
  • Guadalajara
  • Guam
  • Hanoi
  • Harare
  • Hawaii
  • Helsinki
  • Hobart
  • Hong Kong
  • Indiana (East)
  • International Date Line West
  • Irkutsk
  • Islamabad
  • Istanbul
  • Jakarta
  • Jerusalem
  • Kabul
  • Kamchatka
  • Karachi
  • Kathmandu
  • Kolkata
  • Krasnoyarsk
  • Kuala Lumpur
  • Kuwait
  • Kyiv
  • La Paz
  • Lima
  • Lisbon
  • Ljubljana
  • London
  • Madrid
  • Magadan
  • Mazatlan
  • Melbourne
  • Mexico City
  • Mid-Atlantic
  • Minsk
  • Monrovia
  • Monterrey
  • Moscow
  • Mountain Time (US & Canada)
  • Mumbai
  • Muscat
  • Nairobi
  • New Caledonia
  • New Delhi
  • Newfoundland
  • Novosibirsk
  • Nuku'alofa
  • Osaka
  • Pacific Time (US & Canada)
  • Paris
  • Perth
  • Port Moresby
  • Prague
  • Pretoria
  • Quito
  • Riga
  • Riyadh
  • Rome
  • Samoa
  • Santiago
  • Sapporo
  • Sarajevo
  • Saskatchewan
  • Seoul
  • Singapore
  • Skopje
  • Sofia
  • Solomon Is.
  • Sri Jayawardenepura
  • St. Petersburg
  • Stockholm
  • Sydney
  • Taipei
  • Tallinn
  • Tashkent
  • Tbilisi
  • Tehran
  • Tokyo
  • Urumqi
  • Vienna
  • Vilnius
  • Vladivostok
  • Volgograd
  • Warsaw
  • Wellington
  • West Central Africa
  • Yakutsk
  • Yerevan
  • Zagreb
Example Result
ConvertToLocalTime('2007-11-10 23:59:00','Paris') Returns ’2007-11-11 00:59:00’ and the corresponding internal timestamp representation.
ConvertToLocalTime(UTC(), 'GMT-05:00') Returns the time for the North American east coast, for example, New York.
ConvertToLocalTime(UTC(), 'GMT-05:00', True) Returns the time for the North American east coast, for example, New York, without daylight-saving time adjustment.

day

This function returns an integer representing the day when the fraction of the expression is interpreted as a date according to the standard number interpretation.

day(expression)

Return data type: integer

Example Result
day( '1971-10-12' ) returns 12
day( '35648' ) returns 6, because 35648 = 1997-08-06

dayend

This function returns a value corresponding to a timestamp of the final millisecond of the day contained in time. The default output format will be the TimestampFormat set in the script.

DayEnd(time[, [period_no[, day_start]])

Return data type: dual

Argument Description
time The timestamp to evaluate.
period_no period_no is an integer, or expression that resolves to an integer, where the value 0 indicates the day that contains time. Negative values in period_no indicate preceding days and positive values indicate succeeding days.
day_start To specify days not starting at midnight, indicate an offset as a fraction of a day in day_start. For example, 0.125 to denote 3 a.m.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
dayend('25/01/2013 16:45:00') Returns 25/01/2013 23:59:59.
dayend('25/01/2013 16:45:00', -1) Returns '24/01/2013 23:59:59.
dayend('25/01/2013 16:45:00', 0, 0.5) Returns 26/01/2013 11:59:59.

This example finds the timestamp that marks the end of the day after each invoice date in the table.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];

InvoiceData:
LOAD *,
DayEnd(InvDate, 1) AS DEnd
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the dayend() function.
InvDate DEnd
28/03/2012 29/03/2012 23:59:59
10/12/2012 11/12/2012 23:59:59
5/2/2013 07/02/2013 23:59:59
31/3/2013 01/04/2013 23:59:59
19/5/2013 20/05/2013 23:59:59
15/9/2013 16/09/2013 23:59:59
11/12/2013 12/12/2013 23:59:59
2/3/2014 03/03/2014 23:59:59
14/5/2014 15/05/2014 23:59:59
13/6/2014 14/06/2014 23:59:59
7/7/2014 08/07/2014 23:59:59
4/8/2014 05/08/2014 23:59:59

daylightsaving

Returns the current adjustment for daylight saving time, as defined in the running OS.

DaylightSaving( )

Return data type: dual

dayname

This function returns a value showing the date with an underlying numeric value corresponding to a timestamp of the first millisecond of the day containing time.

DayName(time[, period_no [, day_start]])

Return data type: dual

Argument Description
time The timestamp to evaluate.
period_no period_no is an integer, or expression that resolves to an integer, where the value 0 indicates the day that contains time. Negative values in period_no indicate preceding days and positive values indicate succeeding days.
day_start To specify days not starting at midnight, indicate an offset as a fraction of a day in day_start. For example, 0.125 to denote 3 a.m.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
dayname('25/01/2013 16:45:00') Returns 25/01/2013.
dayname('25/01/2013 16:45:00', -1) Returns 24/01/2013.
dayname('25/01/2013 16:45:00', 0, 0.5 )

Returns 25/01/2013.

Displaying the full timestamp shows the underlying numeric value corresponds to '25/01/2013 12:00:00.000.

In this example, the day name is created from the timestamp that marks the beginning of the day after each invoice date in the table.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
DayName(InvDate, 1) AS DName
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the dayname() function.
InvDate DName
28/03/2012 29/03/2012 00:00:00
10/12/2012 11/12/2012 00:00:00
5/2/2013 07/02/2013 00:00:00
31/3/2013 01/04/2013 00:00:00
19/5/2013 20/05/2013 00:00:00
15/9/2013 16/09/2013 00:00:00
11/12/2013 12/12/2013 00:00:00
2/3/2014 03/03/2014 00:00:00
14/5/2014 15/05/2014 00:00:00
13/6/2014 14/06/2014 00:00:00
7/7/2014 08/07/2014 00:00:00
4/8/2014 05/08/2014 00:00:00

daynumberofquarter

This function calculates the day number of the quarter in which a timestamp falls.

DayNumberOfQuarter(timestamp[,start_month])

Return data type: integer

The function always uses years based on 366 days.

Argument Description
timestamp The date to evaluate.
start_month By specifying a start_month between 2 and 12 (1, if omitted), the beginning of the year may be moved forward to the first day of any month. For example, if you want to work with a fiscal year starting March 1, specify start_month = 3.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
DayNumberOfQuarter('12/09/2014') Returns 74, the day number of the current quarter.
DayNumberOfQuarter('12/09/2014',3) Returns 12, the day number of the current quarter.
In this case, the first quarter starts with March (because start_month is specified as 3). This means that the current quarter is the third quarter, which started on September 1.
ProjectTable:
LOAD recno() as InvID, * INLINE [
StartDate
28/03/2014
10/12/2014
5/2/2015
31/3/2015
19/5/2015
15/9/2015
] ;
NrDays:
Load *,
DayNumberOfQuarter(StartDate,4) As DayNrQtr
Resident ProjectTable;
Drop table ProjectTable;
The resulting table shows the returned values of DayNumberOfQuarter for each of the records in the table.
InvID StartDate DayNrQtr
1 28/03/2014 88
2 10/12/2014 71
3 5/2/2015 36
4 31/3/2015 91
5 19/5/2015 49
6 15/9/2015 77

daynumberofyear

This function calculates the day number of the year in which a timestamp falls. The calculation is made from the first millisecond of the first day of the year, but the first month can be offset.

DayNumberOfYear(timestamp[,start_month])

Return data type: integer

The function always uses years based on 366 days.

Argument Description
timestamp The date to evaluate.
start_month By specifying a start_month between 2 and 12 (1, if omitted), the beginning of the year may be moved forward to the first day of any month. For example, if you want to work with a fiscal year starting March 1, specify start_month = 3.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
DayNumberOfYear('12/09/2014') Returns 256, the day number counted from the first of the year.
DayNumberOfYear('12/09/2014',3) Returns 196, the number of the day, as counted from 1 March.
ProjectTable:
LOAD recno() as InvID, * INLINE [
StartDate
28/03/2014
10/12/2014
5/2/2015
31/3/2015
19/5/2015
15/9/2015
] ;
NrDays:
Load *,
DayNumberOfYear(StartDate,4) As DayNrYear
Resident ProjectTable;
Drop table ProjectTable;
The resulting table shows the returned values of DayNumberOfYear for each of the records in the table.
InvID StartDate DayNrYear
1 28/03/2014 363
2 10/12/2014 254
3 5/2/2015 311
4 31/3/2015 366
5 19/5/2015 49
6 15/9/2015 168

daystart

This function returns a value corresponding to a timestamp with the first millisecond of the day contained in the time argument. The default output format will be the TimestampFormat set in the script.

DayStart(time[, [period_no[, day_start]])

Return data type: dual

Argument Description
time The timestamp to evaluate.
period_no period_no is an integer, or expression that resolves to an integer, where the value 0 indicates the day that contains time. Negative values in period_no indicate preceding days and positive values indicate succeeding days.
day_start To specify days not starting at midnight, indicate an offset as a fraction of a day in day_start. For example, 0.125 to denote 3 a.m.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
daystart('25/01/2013 16:45:00') Returns 25/01/2013 00:00:00.
daystart('25/01/2013 16:45:00', -1) Returns 24/01/2013 00:00:00.
daystart('25/01/2013 16:45:00', 0, 0.5 ) Returns 25/01/2013 12:00:00.

This example finds the timestamp that marks the beginning of the day after each invoice date in the table.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
DayStart(InvDate, 1) AS DStart
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the daystart() function.
InvDate DStart
28/03/2012 29/03/2012 00:00:00
10/12/2012 11/12/2012 00:00:00
5/2/2013 07/02/2013 00:00:00
31/3/2013 01/04/2013 00:00:00
19/5/2013 20/05/2013 00:00:00
15/9/2013 16/09/2013 00:00:00
11/12/2013 12/12/2013 00:00:00
2/3/2014 03/03/2014 00:00:00
14/5/2014 15/05/2014 00:00:00
13/6/2014 14/06/2014 00:00:00
7/7/2014 08/07/2014 00:00:00
4/8/2014 05/08/2014 00:00:00

firstworkdate

The firstworkdate function returns the latest starting date to achieve no_of_workdays (Monday-Friday) ending no later than end_date taking into account any optionally listed holidays. end_date and holiday should be valid dates ortimestamps.

firstworkdate(end_date, no_of_workdays {, holiday})

Return data type: integer

Argument Description
end_date The timestamp of end date to evaluate.
no_of_workdays The number of working days to achieve.
holiday Holiday periods to exclude from working days. A holiday period is stated as a start date and an end date, separated by commas.
'25/12/2013', '26/12/2013'
You can specify more than one holiday period, separated by commas.
'25/12/2013', '26/12/2013', '31/12/2013', '01/01/2014'

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
firstworkdate ('29/12/2014', 9) Returns '17/12/2014.
firstworkdate ('29/12/2014', 9, '25/12/2014', '26/12/2014') Returns 15/12/2014 because a holiday period of two days is taken into account.
ProjectTable:
LOAD *, recno() as InvID, INLINE [
EndDate
28/03/2015
10/12/2015
5/2/2016
31/3/2016
19/5/2016
15/9/2016
] ;
NrDays:
Load *,
FirstWorkDate(EndDate,120) As StartDate
Resident ProjectTable;
Drop table ProjectTable;
The resulting table shows the returned values of FirstWorkDate for each of the records in the table.
InvID EndDate StartDate
1 28/03/2015 13/10/2014
2 10/12/2015 26/06/2015
3 5/2/2016 24/08/2015
4 31/3/2016 16/10/2015
5 19/5/2016 04/12/2015
6 15/9/2016 01/04/2016

GMT

This function returns the current Greenwich Mean Time, as derived from the system clock and Windows time settings.

GMT( )

Return data type: dual

hour

This function returns an integer representing the hour when the fraction of the expression is interpreted as a time according to the standard number interpretation.

hour(expression)

Return data type: integer

Example Result
hour( '09:14:36' ) returns 9
hour( '0.5555' ) returns 13 ( Because 0.5555 = 13:19:55 )

inday

This function returns True if timestamp lies inside the day containing base_timestamp.

InDay(timestamp, base_timestamp, period_no[, day_start])

Return data type: Boolean

Argument Description
timestamp The date and time that you want to compare with base_timestamp.
base_timestamp Date and time that is used to evaluate the timestamp.
period_no The day can be offset by period_no. period_no is an integer, where the value 0 indicates the day which contains base_timestamp. Negative values in period_no indicate preceding days and positive values indicate succeeding days.
day_start If you want to work with days not starting midnight, indicate an offset as a fraction of a day in day_start, For example, 0.125 to denote 3 a.m.
Example Result
inday ('12/01/2006 12:23:00', '12/01/2006 00:00:00', 0) Returns True
inday ('12/01/2006 12:23:00', '13/01/2006 00:00', 0) Returns False
inday ('12/01/2006 12:23:00', '12/01/2006 00:00:00', -1) Returns False
inday ('11/01/2006 12:23:00', '12/01/2006 00:00:00', -1) Returns True
inday ('12/01/2006 12:23:00', '12/01/2006 00:00:00', 0, 0.5) Returns False
inday ('12/01/2006 11:23:00', '12/01/2006 00:00:00', 0, 0.5) Returns True
This example checks if an invoice date falls at any time in the day starting with the base_timestamp.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvTime
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InDay(InvTime, '28/03/2012 00:00:00', 0) AS InDayEx
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the inday() function.

InvTime InDayEx
28/03/2012 -1 (True)
10/12/2012 0 (False)
5/2/2013 0 (False)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

indaytotime

This function returns True if timestamp lies inside the part of day containing base_timestamp up until and including the exact millisecond of base_timestamp.

InDayToTime(timestamp, base_timestamp, period_no[, day_start])

Return data type: Boolean

Argument Description
timestamp The date and time that you want to compare with base_timestamp.
base_timestamp Date and time that is used to evaluate the timestamp.
period_no The day can be offset by period_no. period_no is an integer, where the value 0 indicates the day which contains base_timestamp. Negative values in period_no indicate preceding days and positive values indicate succeeding days.
day_start (optional) If you want to work with days not starting midnight, indicate an offset as a fraction of a day in day_start, For example, 0.125 to denote 3 a.m.
Example Result
indaytotime ('12/01/2006 12:23:00', '12/01/2006 23:59:00', 0) Returns True
indaytotime ('12/01/2006 12:23:00', '12/01/2006 00:00:00', 0) Returns False
indaytotime ('11/01/2006 12:23:00', '12/01/2006 23:59:00', -1) Returns True

This example checks if an invoice timestamp falls before 17:00:00 on the day starting with the base_timestamp.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvTime
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InDayToTime(InvTime, '28/03/2012 17:00:00', 0) AS InDayExTT
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the indaytotime() function.

InvTime InDayExTT
28/03/2012 -1 (True)
10/12/2012 0 (False)
5/2/2013 0 (False)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inlunarweek

This function finds if timestamp lies inside the lunar week containing base_date. Lunar weeks are defined by counting 1 January as the first day of the week.

InLunarWeek(timestamp, base_date, period_no[, first_week_day])

Return data type: Boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the lunar week.
period_no The lunar week can be offset by period_no. period_no is an integer, where the value 0 indicates the lunar week which contains base_date.Negative values in period_no indicate preceding lunar weeks and positive values indicate succeeding lunar weeks.
first_week_day An offset that may be greater than or less than zero. This changes the beginning of the year by the specified number of days and/or fractions of a day.
Example Result
inlunarweek('12/01/2013', '14/01/2013', 0) Returns True. Because the value of timestamp, 12/01/2013 falls in the week 08/01/2013 to 14/01/2013.
inlunarweek('12/01/2013', '07/01/2013', 0) Returns False. Because the base_date 07/01/2013 is in the lunar week defined as 01/01/2013 to 07/01/2013.
inlunarweek('12/01/2013', '14/01/2013', -1) Returns False. Because specifying a value of period_no as -1 shifts the week to the previous week, 01/01/2013 to 07/01/2013.
inlunarweek('07/01/2013', '14/01/2013', -1) Returns True. In comparison with the previous example, the timestamp is in the week after taking into account the shift backwards.
inlunarweek('11/01/2006', '08/01/2006', 0, 3) Returns False. Because specifying a value for first_week_day as 3 means the start of the year is calculated from 04/01/2013, and so the value of base_date falls in the first week, and the value of timestamp falls in the week 11/01/2013 to 17/01/2013.

This example checks if an invoice date falls in the week shifted from the value of base_date by four weeks.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InLunarWeek(InvDate, '11/01/2013', 4) AS InLWeekPlus4
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the inlunarweek() function.

The function returns True for the value of InvDate5/2/2013 because the value of base_date, 11/01/2013, is shifted by four weeks, and so falls in the week 5/02/2013 to 11/02/2013.

InvDate InLWeekPlus4
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 -1 (True)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inlunarweektodate

This function finds if timestamp lies inside the part of the lunar week up to and including the last millisecond of base_date. Lunar weeks are defined by counting 1 January as the first day of the week.

InLunarWeekToDate(timestamp, base_date, period_no [, first_week_day])

Return data type: Boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the lunar week.
period_no The lunar week can be offset by period_no. period_no is an integer, where the value 0 indicates the lunar week which contains base_date.Negative values in period_no indicate preceding lunar weeks and positive values indicate succeeding lunar weeks.
first_week_day An offset that may be greater than or less than zero. This changes the beginning of the year by the specified number of days and/or fractions of a day.
Example Result
inlunarweektodate('12/01/2013', '13/01/2013', 0) Returns True. Because the value of timestamp, 12/01/2013 falls in the part of the week 08/01/2013 to 13/01/2013.
inlunarweektodate('12/01/2013', '11/01/2013', 0) Returns False. Because the value of timestamp is later than the value base_date even though the two dates are in the same lunar week before 12/01/2012.
inlunarweektodate('12/01/2006', '05/01/2006', 1) Returns True. Specifying a value of 1 for period_no shifts the base_date forward one week, so the value of timestamp falls in the part of the lunar week.

This example checks if an invoice date falls in the part of the week shifted from the value of base_date by four weeks.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InLunarWeekToDate(InvDate, '07/01/2013', 4) AS InLWeek2DPlus4
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the inlunarweek() function.

The function returns True for the value of InvDate5/2/2013 because the value of base_date, 11/01/2013, is shifted by four weeks, and so falls in the part of the week 5/02/2013 to 07/02/2013.

InvDate InLWeek2DPlus4
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 -1 (True)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inmonth

This function returns True if timestamp lies inside the month containing base_date .

InMonth(timestamp, base_date, period_no [, first_month_of_year])

Return data type: Boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the month.
period_no The month can be offset by period_no. period_no is an integer, where the value 0 indicates the month which contains base_date.Negative values in period_no indicate preceding months and positive values indicate succeeding months.
first_month_of_year The first_month_of_year parameter is disabled and reserved for future use.
Example Result
inmonth ('25/01/2013', '01/01/2013', 0 ) Returns True
inmonth('25/01/2013', '01/04/2013', 0) Returns False
inmonth ('25/01/2013', '01/01/2013', -1) Returns False
inmonth ('25/12/2012', '01/01/2013', -1) Returns True

This example checks if an invoice date falls at any time in the fourth month after the month in base_date, by specifying period_no as 4.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InMonth(InvDate, '31/01/2013', 4) AS InMthPlus4
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the inmonth() function.
InvDate InMthPlus4
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 0 (False)
31/3/2013 0 (False)
19/5/2013 -1 (True)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inmonths

This function finds if a timestamp falls within the same month, bi-month, quarter, tertial, or half-year as a base date.It is also possible to find if the timestamp falls within a previous or following time period.

InMonths(n_months, timestamp, base_date, period_no [, first_month_of_year])

Return data type: Boolean

Argument Description
n_months The number of months that defines the period. An integer or expression that resolves to an integer that must be one of: 1 (equivalent to the inmonth() function), 2 (bi-month), 3 (equivalent to the inquarter() function), 4 (tertial), or 6 (half year).
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the period.
period_no The period can be offset by period_no, an integer, or expression resolving to an integer, where the value 0 indicates the period that contains base_date. Negative values in period_no indicate preceding periods and positive values indicate succeeding periods.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
inmonths(4, '25/01/2013', '25/04/2013', 0) Returns True. Because the value of timestamp, 25/01/2013, lies within the four-month period 01/01/2013 to 30/04/2013, in which the value of base_date, 25/04/2013 lies.
inmonths(4, '25/05/2013', '25/04/2013', 0) Returns False. Because 25/05/2013 is outside the same period as the previous example.
inmonths(4, '25/11/2012', '01/02/2013', -1 ) Returns True. Because the value of period_no, -1, shifts the search period back one period of four months (the value of n-months), which makes the search period 01/09/2012 to 31/12/2012.
inmonths( 4, '25/05/2006', '01/03/2006', 0, 3) Returns True. Because the value of first_month_of_year is set to 3, which makes the search period 01/03/2006 to 30/07/2006 instead of 01/01/2006 to 30/04/2006.

This example checks if the invoice date in the table falls in the bi-month period that includes the base_date shifted forwards by one bi-month period (by specifying period_no as 1).

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InMonths(2, InvDate, '11/02/2013', 1) AS InMthsPlus1
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the InMonths() function.

The search period is 01/03/2013 to 30/04/2013, because the value of base_date is shifted forwards two months from he value in the function (11/02/2013).

InvDate InMthsPlus1
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 0 (False)
31/3/2013 -1 (True)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inmonthstodate

This function finds if a timestamp falls within the part a period of the month, bi-month, quarter, tertial, or half-year up to and including the last millisecond of base_date . It is also possible to find if the timestamp falls within a previous or following time period.

InMonths( n_months, timestamp, base_date, period_no[, first_month_of_year] )

Return data type: Boolean

Argument Description
n_months The number of months that defines the period. An integer or expression that resolves to an integer that must be one of: 1 (equivalent to the inmonth() function), 2 (bi-month), 3 (equivalent to the inquarter() function), 4 (tertial), or 6 (half year).
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the period.
period_no The period can be offset by period_no, an integer, or expression resolving to an integer, where the value 0 indicates the period that contains base_date.Negative values in period_no indicate preceding periods and positive values indicate succeeding periods.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
inmonthstodate(4, '25/01/2013', '25/04/2013', 0) Returns True. Because the value of timestamp, 25/01/2013, lies within the four-month period 01/01/2013 up to the end of 25/04/2013, in which the value of base_date, 25/04/2013 lies.
inmonthstodate(4, '26/04/2013', '25/04/2006', 0) Returns False. Because 26/04/2013 is outside the same period as the previous example.
inmonthstodate(4, '25/09/2005', '01/02/2006', -1) Returns True. Because the value of period_no, -1, shifts the search period back one period of four months (the value of n-months), which makes the search period 01/09/2012 to 01/02/2012.
inmonthstodate(4, '25/04/2006', '01/06/2006', 0, 3) Returns True. Because the value of first_month_of_year is set to 3, which makes the search period 01/03/2006 to 01/06/2006 instead of 01/05/2006 to 01/06/2006.

This example checks if the invoice date in the table falls in the part of the bi-month period up to and including the base_date shifted forwards by four bi-month periods (by specifying period_no as 4).

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InMonthsToDate(2, InvDate, '15/02/2013', 4) AS InMths2DPlus4
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the InMonths() function.

The search period is 01/09/2013 to 15/10/2013, because the value of base_date is shifted forwards eight months from the value in the function (15/02/2013).

InvDate InMths2DPlus4
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 0 (False)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 -1 (True)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inmonthtodate

Returns True if date lies inside the part of month containing basedate up until and including the last millisecond of basedate.

InMonthToDate(timestamp, base_date, period_no)

Return data type: Boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the month.
period_no The month can be offset by period_no. period_no is an integer, where the value 0 indicates the month which contains base_date. Negative values in period_no indicate preceding months and positive values indicate succeeding months.
Example Result
inmonthtodate ('25/01/2013', '25/01/2013', 0) Returns True
inmonthtodate ('25/01/2013', '24/01/2013', 0) Returns False
inmonthtodate ('25/01/2013', '28/02/2013', -1) Returns True

By specifying period_no as 4, this example checks if an invoice date falls in the fourth month after the month in base_date but before the end of the day specified in base_date.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InMonthsToDate(2, InvDate, '15/02/2013', 4) AS InMths2DPlus4
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the inmonthtodate() function.
InvDate InMthPlus42D
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 0 (False)
31/3/2013 0 (False)
19/5/2013 -1 (True)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inquarter

This function returns True if timestamp lies inside the quarter containing base_date .

InQuarter( timestamp, base_date, period_no[, first_month_of_year] )

Return data type: Boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the quarter.
period_no The quarter can be offset by period_no. period_no is an integer, where the value 0 indicates the quarter which contains base_date. Negative values in period_no indicate preceding quarters and positive values indicate succeeding quarters.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.
Example Result
inquarter ('25/01/2013', '01/01/2013', 0) Returns True
inquarter ('25/01/2013', '01/04/2013', 0) Returns False
inquarter ('25/01/2013', '01/01/2013', -1) Returns False
inquarter ('25/12/2012', '01/01/2013', -1) Returns True
inquarter ('25/01/2013', '01/03/2013', 0, 3) Returns False
inquarter ('25/03/2013', '01/03/2013', 0, 3) Returns True

This example checks if an invoice date falls in the fourth quarter of the fiscal year specified by setting the value of first_month_of_year to 4, and having the base_date 31/01/2013.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InQuarter(InvDate, '31/01/2013', 0, 4) AS Qtr4FinYr1213
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the inquarter() function.
InvDate Qtr4Fin1213
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 -1 (True)
31/3/2013 -1 (True)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inquartertodate

This function returns True if timestamp lies inside the part of the quarter containing base_date up until and including the last millisecond of base_date.

InQuarterToDate(timestamp, base_date, period_no [, first_month_of_year])

Return data type: Boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the quarter.
period_no The quarter can be offset by period_no. period_no is an integer, where the value 0 indicates the quarter which contains base_date. Negative values in period_no indicate preceding quarters and positive values indicate succeeding quarters.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.
Example Result
inquartertodate ('25/01/2013', '25/01/2013', 0) Returns True
inquartertodate ( 25/01/2013', '24/01/2013', 0) Returns False
inquartertodate ('25/01/2012', '01/02/2013', -1) Returns True

This example checks if an invoice date falls in a fiscal year specified by setting the value of first_month_of_year to 4, and in the fourth quarter, before the end of 28/02/2013.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InQuarterToDate(InvDate, '28/02/2013', 0, 4) AS Qtr42Date
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the inquartertodate() function.
InvDate Qtr42Date
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 -1 (True)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inweek

This function returns True if timestamp lies inside the week containing base_date.

InWeek(timestamp, base_date, period_no[, first_week_day])

Return data type: boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the week.
period_no The week can be offset by period_no. period_no is an integer, where the value 0 indicates the week which contains base_date. Negative values in period_no indicate preceding weeks and positive values indicate succeeding weeks.
first_week_day By default, the first day of the week is Monday, starting at midnight between Sunday and Monday. To indicate the week starting on another day, specify an offset in first_week_day. This may be given as a whole number of days and/or fractions of a day.
Example Result
inweek ('12/01/2006', '14/01/2006', 0) Returns True
inweek ('12/01/2006', '20/01/2006', 0 ) Returns False
inweek ('12/01/2006', '14/01/2006', -1 ) Returns False
inweek ('07/01/2006', '14/01/2006', -1) Returns True
inweek ('12/01/2006', '09/01/2006', 0, 3) Returns False
Because first_week_day is specified as 3 (Thursday), which makes 12/01/2006 the first day of the week following the week containing 09/01/2006.

This example checks if an invoice date falls at any time in the fourth week after the week in base_date, by specifying period_no as 4.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InWeek(InvDate, '11/01/2013', 4) AS InWeekPlus4
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the inweek() function.

The InvDate5/2/2013 falls within the week that is four weeks after the base_date: 11/1/2013.

InvDate InWeekPlus4
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 -1 (True)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inweektodate

This function returns True if timestamp lies inside the part of week containing base_date up until and including the last millisecond of base_date.

InWeekToDate(timestamp, base_date, period_no [, first_week_day])

Return data type: Boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the week.
period_no The week can be offset by period_no. period_no is an integer, where the value 0 indicates the week which contains base_date. Negative values in period_no indicate preceding weeks and positive values indicate succeeding weeks.
first_week_day By default, the first day of the week is Monday, starting at midnight between Sunday and Monday. To indicate the week starting on another day, specify an offset in first_week_day. This may be given as a whole number of days and/or fractions of a day.
Example Result
inweektodate ('12/01/2006', '12/01/2006', 0) Returns True
inweektodate ('12/01/2006', '11/01/2006', 0) Returns False
inweektodate ('12/01/2006', '18/01/2006', -1) Returns False
Because period_no is specified as -1, the effective data that timestamp is measured against is 11/01/2006.
inweektodate ( '11/01/2006', '12/01/2006', 0, 3 ) Returns False
Because first_week_day is specified as 3 (Thursday), which makes 12/01/2006 the first day of the week following the week containing 12/01/2006.

This example checks if an invoice date falls during the fourth week after the week in base_date, by specifying period_no as 4, but before the value of base_date.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InWeekToDate(InvDate, '11/01/2013', 4) AS InWeek2DPlus4
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the inweek() function.

InvDate InWeek2DPlus4
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 -1 (True)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inyear

This function returns True if timestamp lies inside the year containing base_date .

InYear(timestamp, base_date, period_no [, first_month_of_year])

Return data type: Boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the year.
period_no The year can be offset by period_no. period_no is an integer, where the value 0 indicates the year that contains base_date. Negative values in period_no indicate preceding years, and positive values indicate succeeding years.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
inyear ('25/01/2013', '01/01/2013', 0 ) Returns True
inyear ('25/01/2012', '01/01/2013', 0) Returns False
inyear ('25/01/2013', '01/01/2013', -1) Returns False
inyear ('25/01/2012', '01/01/2013', -1 ) Returns True
inyear ('25/01/2013', '01/01/2013', 0, 3)

Returns True

The value of base_date and first_month_of_year specify that timestamp must fall within 01/03/2012 and 28/02/2013

inyear ('25/03/2013', '01/07/2013', 0, 3 ) Returns True

This example checks if an invoice date falls in the fiscal year specified by setting the value of first_month_of_year to 4, and having the base_date between 1/4/2012 and 31/03/2013.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InYear(InvDate, '31/01/2013', 0, 4) AS FinYr1213
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the inyear() function.
InvDate FinYr1213
28/03/2012 0 (False)
10/12/2012 -1 (True)
5/2/2013 -1 (True)
31/3/2013 -1 (True)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

inyeartodate

This function returns True if timestamp lies inside the part of year containing base_date up until and including the last millisecond of base_date.

InYearToDate(timestamp, base_date, period_no[, first_month_of_year])

Return data type: Boolean

Argument Description
timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the year.
period_no The year can be offset by period_no. period_no is an integer, where the value 0 indicates the year that contains base_date.Negative values in period_no indicate preceding years, and positive values indicate succeeding years.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.
Example Result
inyeartodate ('2013/01/25', '2013/02/01', 0) Returns True
inyeartodate ('2012/01/25', '2013/01/01', 0) Returns False
inyeartodate ('2012/01/25', '2013/02/01', - ) Returns True
inyeartodate ('2012/11/25', '2013/01/31', 0, 4) Returns True
The value of timestamp falls inside the fiscal year beginning in the fourth month and before the value of base_date.
inyeartodate ( '2013/3/31', '2013/01/31', 0, 4 ) Returns False
Compared with the previous example, the value of timestamp is still inside the fiscal year, but it is after the value of base_date, so it falls outside the part of the year.

This example checks if an invoice date falls in a fiscal year specified by setting the value of first_month_of_year to 4, and in the part of the year before the end of 31/01/2013.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
InYearToDate(InvDate, '31/01/2013', 0, 4) AS FinYr2Date
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the inyeartodate() function.
InvDate FinYr2Date
28/03/2012 0 (False)
10/12/2012 -1 (True)
5/2/2013 0 (False)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 0 (False)
11/12/2013 0 (False)
2/3/2014 0 (False)
14/5/2014 0 (False)
13/6/2014 0 (False)
7/7/2014 0 (False)
4/8/2014 0 (False)

lastworkdate

The lastworkdate function returns the earliest ending date to achieve no_of_workdays (Monday-Friday) if starting at start_date taking into account any optionally listed. start_date and holiday should be valid dates or timestamps.

Lastworkdate(start_date, no_of_workdays {, holiday})

Return data type: dual

Argument Description
start_date The start date to evaluate.
no_of_workdays The number of working days to achieve.
holiday

Holiday periods to exclude from working days. A holiday period is stated as a start date and an end date, separated by commas.

'25/12/2013', '26/12/2013'

You can specify more than one holiday period, separated by commas.

'25/12/2013', '26/12/2013', '31/12/2013', '01/01/2014'

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
lastworkdate ('19/12/2014', 9) Returns '31/12/2014'  
lastworkdate ('19/12/2014', 9, '2014-12-25', '2014-12-26') Returns '02/01/2015 as a holiday period of two days is taken into account.
ProjectTable:
LOAD *, recno() as InvID, INLINE [
StartDate
28/03/2014
10/12/2014
5/2/2015
31/3/2015
19/5/2015
15/9/2015
] ;
NrDays:
Load *,
LastWorkDate(StartDate,120) As EndDate
Resident ProjectTable;
Drop table ProjectTable;
The resulting table shows the returned values of LastWorkDate for each of the records in the table.
InvID StartDate EndDate
1 28/03/2014 11/09/2014
2 10/12/2014 26/05/2015
3 5/2/2015 27/07/2015
4 31/3/2015 14/09/2015
5 19/5/2015 02/11/2015
6 15/9/2015 29/02/2016

localtime

This function returns a timestamp of the current time from the system clock for a specified time zone.

LocalTime([timezone [, ignoreDST ]])

Return data type: dual

Argument Description
timezone The timezone is specified as a string containing any of the geographical places listed under Time Zone in the Windows Control Panel for Date and Time or as a string in the form 'GMT+hh:mm'.
If no time zone is specified the local time will be returned.
ignoreDST If ignoreDST is -1 (True) daylight savings time will be ignored.

The examples below are based on the function being called on 2014-10-22 12:54:47 local time, with the local time zone being GMT+01:00.

Example Result
localtime () Returns the local time 2014-10-22 12:54:47.
localtime ('London') Returns the local time in London, 2014-10-22 11:54:47.
localtime ('GMT+02:00') Returns the local time in the timezone of GMT+02:00, 2014-10-22 13:54:47.
localtime ('Paris','-1') Returns the local time in Paris with daylight savings time ignored, 2014-10-22 11:54:47.

lunarweekend

This function returns a value corresponding to a timestamp of the last millisecond of the lunar week containing date. Lunar weeks are defined by counting 1 January as the first day of the week.

LunarweekEnd(date[, period_no[, first_week_day]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer or expression resolving to an integer, where the value 0 indicates the lunar week which contains date . Negative values in period_no indicate preceding lunar weeks and positive values indicate succeeding lunar weeks.
first_week_day An offset that may be greater than or less than zero. This changes the beginning of the year by the specified number of days and/or fractions of a day.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
lunarweekend('12/01/2013') Returns 14/01/2013 23:59:59.
lunarweekend('12/01/2013', -1) Returns 7/01/2013 23:59:59.
lunarweekend('12/01/2013', 0, 1) Returns 15/01/2013 23:59:59.

This example finds the final day of the lunar week of each invoice date in the table, where the date is shifted by one week by specifying period_no as 1.

<TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
LunarWeekEnd(InvDate, 1) AS LWkEnd
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the lunarweekend() function.
InvDate LWkEnd
28/03/2012 07/04/2012
10/12/2012 22/12/2012
5/2/2013 18/02/2013
31/3/2013 08/04/2013
19/5/2013 27/05/2013
15/9/2013 23/09/2013
11/12/2013 23/12/2013
2/3/2014 11/03/2014
14/5/2014 27/05/2014
13/6/2014 24/06/2014
7/7/2014 15/07/2014
4/8/2014 12/08/2014

lunarweekname

This function returns a display value showing the year and lunar week number corresponding to a timestamp of the first millisecond of the first day of the lunar week containing date. Lunar weeks are defined by counting 1 January as the first day of the week.

LunarWeekName(date [, period_no[, first_week_day]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer or expression resolving to an integer, where the value 0 indicates the lunar week which contains date . Negative values in period_no indicate preceding lunar weeks and positive values indicate succeeding lunar weeks.
first_week_day An offset that may be greater than or less than zero. This changes the beginning of the year by the specified number of days and/or fractions of a day.
Example Result
lunarweekname('12/01/2013') Returns 2006/02.
lunarweekname('12/01/2013', -1) Returns 2006/01.
lunarweekname('12/01/2013', 0, 1) Returns 2006/02.

In this example, for each invoice date in the table, the lunar week name is created from the year in which the week lies and its associated lunar week number, shifted one week by specifying period_no as 1.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
LunarWeekName(InvDate, 1) AS LWkName
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the lunarweekname() function.
InvDate LWkName
28/03/2012 2012/14
10/12/2012 2012/51
5/2/2013 2013/07
31/3/2013 2013/14
19/5/2013 2013/21
15/9/2013 2013/38
11/12/2013 2013/51
2/3/2014 2014/10
14/5/2014 2014/21
13/6/2014 2014/25
7/7/2014 2014/28
4/8/2014 2014/32

lunarweekstart

This function returns a value corresponding to a timestamp of the first millisecond of the lunar week containing date. Lunar weeks are defined by counting 1 January as the first day of the week.

LunarweekStart(date[, period_no[, first_week_day]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer or expression resolving to an integer, where the value 0 indicates the lunar week which contains date . Negative values in period_no indicate preceding lunar weeks and positive values indicate succeeding lunar weeks.
first_week_day An offset that may be greater than or less than zero. This changes the beginning of the year by the specified number of days and/or fractions of a day.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
lunarweekstart('12/01/2013') Returns 08/01/2013.
lunarweekstart('12/01/2013', -1) Returns 01/01/2013.
lunarweekstart('12/01/2013', 0, 1 ) Returns 09/01/2013.
Because the offset specified by setting first_week_day to 1 means the beginning of the year is changed to 02/01/2013.

This example finds the first day of the lunar week of each invoice date in the table, where the date is shifted by one week by specifying period_no as 1.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
LunarWeekStart(InvDate, 1) AS LWkStart
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the lunarweekstart() function.
InvDate LWkStart
28/03/2012 01/04/2012
10/12/2012 16/12/2012
5/2/2013 12/02/2013
31/3/2013 02/04/2013
19/5/2013 21/05/2013
15/9/2013 17/09/2013
11/12/2013 17/12/2013
2/3/2014 05/03/2014
14/5/2014 21/05/2014
13/6/2014 18/06/2014
7/7/2014 09/07/2014
4/8/2014 06/08/2014

makedate

This function returns a date calculated from the year YYYY, the month MM and the day DD.

MakeDate(YYYY [, MM [, DD ]])

Return data type: dual

Argument Description
YYYY The year as an integer.
MM The month as an integer. If no month is stated, 1 (January) is assumed.
DD The day as an integer.

If no day is stated, 1 (the 1st) is assumed.

Example Result
makedate(2012) returns 2012-01-01
makedate(12) returns 0012-01-01
makedate(2012,12) returns 2012-12-01
makedate(2012,2,14) returns 2012-02-14

maketime

This function returns a time calculated from the hour hh, the minute mm, and the second ss.

MakeTime(hh [, mm [, ss ]])

Return data type: dual

Argument Description
hh The hour as an integer.
mm

The minute as an integer.

If no minute is stated, 00 is assumed.

ss

The second as an integer.

If no second is stated, 00 is assumed.

Example Result
maketime( 22 ) returns 22:00:00
maketime( 22, 17 ) returns 22:17:00
maketime( 22, 17, 52 ) returns 22:17:52

makeweekdate

This function returns a date calculated from the year YYYY ,the week WW and the day-of-week D.

MakeWeekDate(YYYY [, WW [, D ]])

Return data type: dual

Argument Description
YYYY The year as an integer.
WW The week as an integer.
D

The day-of-week as an integer.

If no day-of-week is stated, 0 (Monday) is assumed.

Example Result
makeweekdate(2014,6,6) returns 2014-02-09
makeweekdate(2014,6,1) returns 2014-02-04
makeweekdate(2014,6) returns 2014-02-03 (weekday 0 is assumed)

minute

This function returns an integer representing the minute when the fraction of the expression is interpreted as a time according to the standard number interpretation.

Minute(expression)

Return data type: integer

Example Result
minute ( '09:14:36' ) returns 14
minute ( '0.5555' ) returns 19 ( Because 0.5555 = 13:19:55 )

month

This function returns a dual value: a month name as defined in the environment variable MonthNames and an integer between 1-12. The month is calculated from the date interpretation of the expression, according to the standard number interpretation.

Month(expression)

Return data type: dual

Example Result
month( '2012-10-12' ) returns Oct
month( '35648' ) returns Aug, because 35648 = 1997-08-06

monthend

This function returns a value corresponding to a timestamp of the last millisecond of the last day of the month containing date. The default output format will be the DateFormat set in the script.

MonthEnd(date[, period_no])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer, which, if 0 or omitted, indicates the month that contains date . Negative values in period_no indicate preceding months and positive values indicate succeeding months.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
monthend('19/02/2012') Returns 29/02/2012 23:59:59.
monthend('19/02/2001', -1) Returns 31/01/2001 23:59:59.

This example finds the last day in the month of each invoice date in the table, where the base date is shifted by four months by specifying period_no as 4.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
MonthEnd(InvDate, 4) AS MthEnd
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the monthend() function.
InvDate MthEnd
28/03/2012 31/07/2012
10/12/2012 30/04/2013
5/2/2013 30/06/2013
31/3/2013 31/07/2013
19/5/2013 30/09/2013
15/9/2013 31/01//2014
11/12/2013 30/04//2014
2/3/2014 31/07//2014
14/5/2014 30/09/2014
13/6/2014 31/10/2014
7/7/2014 30/11/2014
4/8/2014 31/12/2014

monthname

This function returns a display value showing the month (formatted according to the MonthNames script variable) and year with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the month.

MonthName(date[, period_no])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer, which, if 0 or omitted, indicates the month that contains date . Negative values in period_no indicate preceding months and positive values indicate succeeding months.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
monthname('19/10/2013') Returns Oct 2013.
Because in this and the other examples, the SET Monthnames statement is set to Jan;Feb;Mar, and so on.
monthname('19/10/2013', -1) Returns Sep 2013.

In this example, for each invoice date in the table, the month name is created from the month name shifted four months from base_date, and from the year.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
MonthName(InvDate, 4) AS MthName
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the monthname() function. In
InvDate MthName
28/03/2012 Jul 2012
10/12/2012 Apr 2013
5/2/2013 Jun 2013
31/3/2013 Jul 2013
19/5/2013 Sep 2013
15/9/2013 Jan 2014
11/12/2013 Apr 2014
2/3/2014 Jul 2014
14/5/2014 Sep 2014
13/6/2014 Oct 2014
7/7/2014 Nov 2014
4/8/2014 Dec 2014

monthsend

This function returns a value corresponding to a timestamp of the lastmillisecond of the month, bi-month, quarter, tertial, or half-year containing a base date. It is also possible to find the timestamp for a previous or following time period.

MonthsEnd(n_months, date[, period_no [, first_month_of_year]])

Return data type: dual

Argument Description
n_months The number of months that defines the period. An integer or expression that resolves to an integer that must be one of: 1 (equivalent to the inmonth() function), 2 (bi-month), 3 (equivalent to the inquarter() function), 4 (tertial), or 6 (half year).
date The date to evaluate.
period_no The period can be offset by period_no, an integer, or expression resolving to an integer, where the value 0 indicates the period that contains base_date.Negative values in period_no indicate preceding periods and positive values indicate succeeding periods.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
monthsend(4, '19/07/2013') Returns 31/08/2013.
monthsend(4, '19/10/2013', -1) Returns 31/08/2013.
monthsend(4, '19/10/2013', 0, 2) Returns 31/01/2014.
Because the start of the year becomes month 2.

This example finds the end of the final day of bi-month period for each invoice date, shifted forwards by one bi-month period.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
MonthsEnd(2, InvDate, 1) AS BiMthsEnd
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the MonthsEnd() function.

InvDate BiMthsEnd
28/03/2012 30/06/2012
10/12/2012 28/02/2013
5/2/2013 30/04/2013
31/3/2013 30/04/2013
19/5/2013 31/08/2013
15/9/2013 31/12/2013
11/12/2013 28/02/2014
2/3/2014 30/06/2014
14/5/2014 31/08/2014
13/6/2014 31/08/2014
7/7/2014 31/10/2014
4/8/2014 31/10/2014

monthsname

This function returns a display value representing the range of the months of the period (formatted according to the MonthNames script variable) as well as the year. The underlying numeric value corresponds to a timestamp of the first millisecond of the month, bi-month, quarter, tertial, or half-year containing a base date.

MonthsName(n_months, date[, period_no[, first_month_of_year]])

Return data type: dual

Argument Description
n_months The number of months that defines the period. An integer or expression that resolves to an integer that must be one of: 1 (equivalent to the inmonth() function), 2 (bi-month), 3 (equivalent to the inquarter() function), 4 (tertial), or 6 (half year).
date The date to evaluate.
period_no The period can be offset by period_no, an integer, or expression resolving to an integer, where the value 0 indicates the period that contains base_date.Negative values in period_no indicate preceding periods and positive values indicate succeeding periods.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
monthsname(4, '19/10/2013') Returns 'Sep-Dec 2013.
Because in this and the other examples, the SET Monthnames statement is set to Jan;Feb;Mar, and so on.
monthsname(4, '19/10/2013', -1) Returns 'May-Aug 2013.
monthsname(4, '19/10/2013', 0, 2) Returns Oct-Jan 2014.
Because the year is specified to begin in month 2, therefore the four-month period ends on the first month of the following year.

In this example, for each invoice date in the table, the months name is created from the range of months in the bi-month period, and from the year. The range is offset by 4x2 months by specifying period_no as 4.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
MonthsName(2, InvDate, 4) AS MthsName
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the monthsname() function.
InvDate MthsName
28/03/2012 Nov-Dec 2012
10/12/2012 Jul-Aug 2013
5/2/2013 Sep-Oct 2013
31/3/2013 Nov-Dec2013
19/5/2013 Jan-Feb 2014
15/9/2013 May-Jun 2014
11/12/2013 Jul-Aug 2014
2/3/2014 Nov-Dec 2014
14/5/2014 Jan-Feb 2015
13/6/2014 Jan-Feb 2015
7/7/2014 Mar-Apr 2015
4/8/2014 Mar-Apr 2015

monthsstart

This function returns a value corresponding to the timestamp of the first millisecond of the month, bi-month, quarter, tertial, or half-year containing a base date. It is also possible to find the timestamp for a previous or following time period.

MonthsStart(n_months, date[, period_no [, first_month_of_year]])

Return data type: dual

Argument Description
n_months The number of months that defines the period. An integer or expression that resolves to an integer that must be one of: 1 (equivalent to the inmonth() function), 2 (bi-month), 3 (equivalent to the inquarter() function), 4 (tertial), or 6 (half year).
date The date to evaluate.
period_no The period can be offset by period_no, an integer, or expression resolving to an integer, where the value 0 indicates the period that contains base_date.Negative values in period_no indicate preceding periods and positive values indicate succeeding periods.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
monthsstart(4, '19/10/2013') Returns 1/09/2013.
monthsstart(4, '19/10/2013, -1) Returns 01/05/2013.
monthsstart(4, '19/10/2013', 0, 2 ) Returns 01/10/2013.
Because the start of the year becomes month 2.

This example finds the first day of the bi-month period for each invoice date, shifted forwards by one bi-month period.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
MonthsStart(2, InvDate, 1) AS BiMthsStart
Resident TempTable;
Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the MonthsStart() function.

InvDate BiMthsStart
28/03/2012 01/05/2012
10/12/2012 01/01/2013
5/2/2013 01/03/2013
31/3/2013 01/05/2013
19/5/2013 01/07/2013
15/9/2013 01/11/2013
11/12/2013 01/01/2014
2/3/2014 01/05/2014
14/5/2014 01/07/2014
13/6/2014 01/07/2014
7/7/2014 01/09/2014
4/8/2014 01/09/2014

monthstart

This function returns a value corresponding to a timestamp of the first millisecond of the first day of the month containing date. The default output format will be the DateFormat set in the script.

MonthStart(date[, period_no])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer, which, if 0 or omitted, indicates the month that contains date. Negative values in period_no indicate preceding months and positive values indicate succeeding months.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
monthstart('19/10/2001') Returns 01/10/2001.
monthstart('19/10/2001', -1) Returns 01/09/2001.

This example finds the first day in the month of each invoice date in the table, where the base_date is shifted by four months by specifying period_no as 4.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
MonthStart(InvDate, 4) AS MthStart
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the monthstart() function.
InvDate MthStart
28/03/2012 01/07/2012
10/12/2012 01/04/2013
5/2/2013 01/06/2013
31/3/2013 01/07/2013
19/5/2013 01/09/2013
15/9/2013 01/01/2014
11/12/2013 01/04/2014
2/3/2014 01/07/2014
14/5/2014 01/09/2014
13/6/2014 01/10/2014
7/7/2014 01/11/2014
4/8/2014 01/12/2014

networkdays

The networkdays function returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holiday.

Networkdays(start_date, end_date [, holiday])

Return data type: integer

Argument Description
start_date The start date to evaluate.
end_date The end date to evaluate.
holiday Holiday periods to exclude from working days. A holiday period is stated as a start date and an end date, separated by commas.
'25/12/2013', '26/12/2013'
You can specify more than one holiday period, separated by commas.
'25/12/2013', '26/12/2013', '31/12/2013', '01/01/2014'

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
networkdays ('19/12/2013', '07/01/2014') Returns 14. This example does not take holidays into account.
networkdays ('19/12/2013', '07/01/2014', '25/12/2013', '26/12/2013') Returns 12. This example takes the holiday 25/12/2013 to 26/12/2013 into account.
networkdays ('19/12/2013', '07/01/2014', '25/12/2013', '26/12/2013', '31/12/2013', '01/01/2014') Returns 10. This example takes two holiday periods into account.
PayTable:
LOAD recno() as InvID, * INLINE [
InvRec|InvPaid
28/03/2012|28/04/2012
10/12/2012|01/01/2013
5/2/2013|5/3/2013
31/3/2013|01/5/2013
19/5/2013|12/6/2013
15/9/2013|6/10/2013
11/12/2013|12/01/2014
2/3/2014|2/4/2014
14/5/2014|14/6/2014
13/6/2014|14/7/2014
7/7/2014|14/8/2014
4/8/2014|4/9/2014
] (delimiter is '|');
NrDays:
Load *,
NetWorkDays(InvRec,InvPaid) As PaidDays
Resident PayTable;
Drop table PayTable;
The resulting table shows the returned values of NetworkDays for each of the records in the table.
InvID InvRec InvPaid PaidDays
1 28/03/2012 28/04/2012 23
2 10/12/2012 01/01/2013 17
3 5/2/2013 5/3/2013 21
4 31/3/2013 01/5/2013 23
5 19/5/2013 12/6/2013 18
6 15/9/2013 6/10/2013 15
7 11/12/2013 12/01/2014 23
8 2/3/2014 2/4/2014 23
9 14/5/2014 14/6/2014 23
10 13/6/2014 14/7/2014 22
11 7/7/2014 14/8/2014 29
12 4/8/2014 4/9/2014 24

now

This function returns a timestamp of the current time from the system clock. The default value is 1.

Now([timer_mode])

Return data type: dual

Argument Description
timer_mode Can have the following values:
0 (time at last finished data load)
1 (time at function call)
2 (time when the app was opened)

Note

If you use the function in a data load script, timer_mode=0 will result in the time of the last finished data load, while timer_mode=1 will give the time of the function call in the current data load.

Example Result
now(0) Returns the time when the last data load completed.
now(1) - When used in a visualization expression, this returns the time of the function call.
- When used in a data load script, this returns the time of the function call in the current data load.
now(2) Returns the time when the app was opened.

quarterend

This function returns a value corresponding to a timestamp of the last millisecond of the quarter containing date. The default output format will be the DateFormat set in the script.

QuarterEnd(date[, period_no[, first_month_of_year]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer, where the value 0 indicates the quarter which contains date. Negative values in period_no indicate preceding quarters and positive values indicate succeeding quarters.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
quarterend('29/10/2005') Returns 31/12/2005 23:59:59.
quarterend('29/10/2005', -1) Returns 30/09/2005 23:59:59.
quarterend('29/10/2005', 0, 3) Returns 30/11/2005 23:59:59.

This example finds the last day in the quarter of each invoice date in the table, where the first month in the year is specified as month 3.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
QuarterEnd(InvDate, 0, 3) AS QtrEnd
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the quarterend() function.
InvDate QtrEnd
28/03/2012 31/05/2012
10/12/2012 28/02/2013
5/2/2013 28/02/2013
31/3/2013 31/05/2013
19/5/2013 31/05/2013
15/9/2013 30/11/2013
11/12/2013 28/02/2014
2/3/2014 31/05/2014
14/5/2014 31/05/2014
13/6/2014 31/08/2014
7/7/2014 31/08/2014
4/8/2014 31/08/2014

quartername

This function returns a display value showing the months of the quarter (formatted according to the MonthNames script variable) and year with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the quarter.

QuarterName(date[, period_no[, first_month_of_year]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer, where the value 0 indicates the quarter which contains date. Negative values in period_no indicate preceding quarters and positive values indicate succeeding quarters.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.
Example Result
quartername('29/10/2013') Returns Oct-Dec 2013.
quartername('29/10/2013', -1) Returns Jul-Sep 2013.
quartername('29/10/2013', 0, 3) Returns Sep-Nov 2013.

In this example, for each invoice date in the table, the quarter name is created based on the quarter containing InvID. The first month of the year is specified as month 4.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
QuarterName(InvDate, 0, 4) AS QtrName
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the quartername() function.
InvDate QtrName
28/03/2012 Jan-Mar 2011
10/12/2012 Oct-Dec 2012
5/2/2013 Jan-Mar 2012
31/3/2013 Jan-Mar 2012
19/5/2013 Apr-Jun 2013
15/9/2013 Jul-Sep 2013
11/12/2013 Oct-Dec 2013
2/3/2014 Jan-Mar 2013
14/5/2014 Apr-Jun 2014
13/6/2014 Apr-Jun 2014
7/7/2014 Jul-Sep 2014
4/8/2014 Jul-Sep 2014

quarterstart

This function returns a value corresponding to a timestamp of the first millisecond of the quarter containing date. The default output format will be the DateFormat set in the script.

QuarterStart(date[, period_no[, first_month_of_year]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer, where the value 0 indicates the quarter which contains date. Negative values in period_no indicate preceding quarters and positive values indicate succeeding quarters.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year .

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
quarterstart('29/10/2005') Returns 01/10/2005.
quarterstart('29/10/2005', -1 ) Returns 01/07/2005.
quarterstart('29/10/2005', 0, 3) Returns 01/09/2005.

This example finds the first day in the quarter of each invoice date in the table, where the first month in the year is specified as month 3.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
QuarterStart(InvDate, 0, 3) AS QtrStart
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the quarterstart() function.
InvDate QtrStart
28/03/2012 01/03/2012
10/12/2012 01/12/2012
5/2/2013 01/12/2012
31/3/2013 01/03/2013
19/5/2013 01/03/2013
15/9/2013 01/09/2013
11/12/2013 01/12/2013
2/3/2014 01/03/2014
14/5/2014 01/03/2014
13/6/2014 01/06/2014
7/7/2014 01/06/2014
4/8/2014 01/06/2014

second

This function returns an integer representing the second when the fraction of the expression is interpreted as a time according to the standard number interpretation.

second(expression)

Return data type: integer

Example Result
second( '09:14:36' ) returns 36
second( '0.5555' ) returns 55 ( Because 0.5555 = 13:19:55 )

setdateyear

This function takes as input a timestamp and a year and updates the timestamp with the year specified in input.

setdateyear(timestamp, year)

Return data type: dual

Argument Description
timestamp A standard Qlik timestamp (often just a date).
year A four-digit year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
setdateyear ('29/10/2005', 2013) Returns '29/10/2013
setdateyear ('29/10/2005 04:26:14', 2013)

Returns '29/10/2013 04:26:14'
To see the time part of the timestamp in a visualization, you must set the number formatting to Date and choose a value for Formatting that displays time values.

SetYear:
Load *,
SetDateYear(testdates, 2013) as NewYear
Inline [
testdates
1/11/2012
10/12/2012
1/5/2013
2/1/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
The resulting table contains the original dates and a column in which the year has be set to 2013.
testdates NewYear
1/11/2012 1/11/2013
10/12/2012 10/12/2013
2/1/2012 2/1/2013
1/5/2013 1/5/2013
19/5/2013 19/5/2013
15/9/2013 15/9/2013
11/12/2013 11/12/2013
2/3/2014 2/3/2013
14/5/2014 14/5/2013
13/6/2014 13/6/2013
7/7/2014 7/7/2013
4/8/2014 4/8/2013

setdateyearmonth

This function takes as input a timestamp, a month and a year and updates the timestamp with the year and the month specified in input.

SetDateYearMonth(timestamp, year, month)

Return data type: dual

Argument Description
timestamp A standard Qlik timestamp (often just a date).
year A four-digit year.
month A one or two-digit month.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
setdateyearmonth ('29/10/2005', 2013, 3) Returns '29/03/2013
setdateyearmonth ('29/10/2005 04:26:14', 2013, 3) Returns '29/03/2013 04:26:14'

To see the time part of the timestamp in a visualization, you must set the number formatting to Date and choose a value for Formatting that displays time values.

SetYearMonth:
Load *,
SetDateYearMonth(testdates, 2013,3) as NewYearMonth
Inline [
testdates
1/11/2012
10/12/2012
2/1/2013
19/5/2013
15/9/2013
11/12/2013
14/5/2014
13/6/2014
7/7/2014
4/8/2014
The resulting table contains the original dates and a column in which the year has be set to 2013.
testdates NewYearMonth
1/11/2012 1/3/2013
10/12/2012 10/3/2013
2/1/2012 2/3/2013
19/5/2013 19/3/2013
15/9/2013 15/3/2013
11/12/2013 11/3/2013
14/5/2014 14/3/2013
13/6/2014 13/3/2013
7/7/2014 7/3/2013
4/8/2014 4/3/2013

timezone

This function returns the name of the current time zone, as defined in the operating system.

TimeZone()

Return data type: string

today

This function returns the current date from the system clock.

Today([timer_mode])

Return data type: dual

Argument Description
timer_mode

Can have the following values:

0 (day of last finished data load)
1 (day of function call)
2 (day when the app was opened)

If you use the function in a data load script, timer_mode=0 will result in the day of the last finished data load, while timer_mode=1 will give the day of the current data load.
Example Result

Today(0)

Returns the day of the last finished data load.

Today(1)

Returns the day when the current data load started

Today(2)

Returns the day when the app was opened.

UTC

Returns the current Coordinated Universal Time.

UTC()

Return data type: dual

week

This function returns an integer representing the week number according to ISO 8601. The week number is calculated from the date interpretation of the expression, according to the standard number interpretation.

week(timestamp [, first_week_day [, broken_weeks [, reference_day]]])

Return data type: integer

Argument Description
timestamp The date to evaluate as a timestamp or expression resolving to a timestamp, to convert, for example '2012-10-12'.
first_week_day

If you don't specify first_week_day, the value of variable FirstWeekDay will be used as the first day of the week.

If you want to use another day as the first day of the week, set first_week_day to:

  • 0 for Monday
  • 1 for Tuesday
  • 2 for Wednesday
  • 3 for Thursday
  • 4 for Friday
  • 5 for Saturday
  • 6 for Sunday

The integer returned by the function will now use the first day of the week that you set with first_week_day.

broken_weeks

If you don't specify broken_weeks, the value of variable BrokenWeeks will be used to define if weeks are broken or not.

By default functions use unbroken weeks. This means that:

  • In some years, week 1 starts in December, and in other years, week 52 or 53 continues into January.
  • Week 1 always has at least 4 days in January.

The alternative is to use broken weeks.

  • Week 52 or 53 do not continue into January.
  • Week 1 starts on January 1 and is, in most cases, not a full week.

The following values can be used:

  • 0 (=use unbroken weeks)
  • 1 (= use broken weeks)
reference_day

If you don't specify reference_day, the value of variable ReferenceDay will be used to define which day in January to set as reference day to define week 1. By default, functions use 4 as the reference day. This means that week 1 must contain January 4, or put differently, that week 1 must always have at least 4 days in January.

The following values can be used to set a different reference day:

  • 1 (= January 1)
  • 2 (= January 2)
  • 3 (= January 3)
  • 4 (= January 4)
  • 5 (= January 5)
  • 6 (= January 6)
  • 7 (= January 7)
Example Result
week( '2012-10-12' ) returns 41.
week( '35648' ) returns 32, because 35648 = 1997-08-06
week('2012-10-12', 0, 1) returns 42

weekday

This function returns a dual value with:

  • A day name as defined in the environment variable DayNames.
  • An integer between 0-6 corresponding to the nominal day of the week (0-6).

weekday(date [,first_week_day=0])

Return data type: dual

Argument Description
date The date to evaluate.
first_week_day

If you don't specify first_week_day, the value of variable FirstWeekDay will be used as the first day of the week.

If you want to use another day as the first day of the week, set first_week_day to:

  • 0 for Monday
  • 1 for Tuesday
  • 2 for Wednesday
  • 3 for Thursday
  • 4 for Friday
  • 5 for Saturday
  • 6 for Sunday

The integer returned by the function will now use the first day of the week that you set with first_week_day as base (0).

Unless it is stated specifically, FirstWeekDay is set to 0 in these examples.

Example Result

weekday( '1971-10-12' )

returns 'Tue' and 1

weekday( '1971-10-12' , 6) returns 'Tue' and 2.
In this example we use Sunday (6) as the first day of the week.
SET FirstWeekDay = 6;
...
weekday( '1971-10-12')
returns 'Tue' and 2.

weekend

This function returns a value corresponding to a timestamp of the last millisecond of the last day (Sunday) of the calendar week containing date The default output format will be the DateFormat set in the script.

WeekEnd(date [, period_no[, first_week_day]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks.
first_week_day

Specifies the day on which the week starts. If omitted, the value of variable FirstWeekDay is used.

The possible values first_week_day are:

  • 0 for Monday
  • 1 for Tuesday
  • 2 for Wednesday
  • 3 for Thursday
  • 4 for Friday
  • 5 for Saturday
  • 6 for Sunday

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
weekend('10/01/2013') Returns 12/01/2013 23:59:59.
weekend('10/01/2013', -1) Returns 06/01/2013 23:59:59.
weekend('10/01/2013', 0, 1) Returns 14/01/2013 23:59:59.

This example finds the final day in the week following the week of each invoice date in the table.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
WeekEnd(InvDate, 1) AS WkEnd
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the weekend() function.
InvDate WkEnd
28/03/2012 08/04/2012
10/12/2012 23/12/2012
5/2/2013 17/02/2013
31/3/2013 07/04/2013
19/5/2013 26/05/2013
15/9/2013 22/09/2013
11/12/2013 22/12/2013
2/3/2014 09/03/2014
14/5/2014 25/05/2014
13/6/2014 22/06/2014
7/7/2014 20/07/2014
4/8/2014 17/08/2014

weekname

This function returns a value showing the year and week number with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the week containing date.

WeekName(date[, period_no[,first_week_day]])

Return data type: dual

Argument Description
date The date to evaluate.
perio_noshift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks.
first_week_day

Specifies the day on which the week starts. If omitted, the value of variable FirstWeekDay is used.

The possible values first_week_day are:

  • 0 for Monday
  • 1 for Tuesday
  • 2 for Wednesday
  • 3 for Thursday
  • 4 for Friday
  • 5 for Saturday
  • 6 for Sunday
Example Result
weekname('12/01/2013') Returns 2013/02.
weekname('12/01/2013', -1) Returns 2013/01.
weekname('12/01/2013', 0, 1) Returns '2013/02.

In this example, for each invoice date in the table, the week name is created from the year in which the week lies and its associated week number, shifted one week by specifying period_no as 1.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
WeekName(InvDate, 1) AS WkName
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the weekname() function.
InvDate WkName
28/03/2012 2012/14
10/12/2012 2012/51
5/2/2013 2013/07
31/3/2013 2013/14
19/5/2013 2013/21
15/9/2013 2013/38
11/12/2013 2013/51
2/3/2014 2014/10
14/5/2014 2014/21
13/6/2014 2014/25
7/7/2014 2014/29
4/8/2014 2014/33

weekstart

This function returns a value corresponding to a timestamp of the first millisecond of the first day (Monday) of the calendar week containing date. The default output format is the DateFormat set in the script.

WeekStart(date [, period_no[, first_week_day]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks.
first_week_day

Specifies the day on which the week starts. If omitted, the value of variable FirstWeekDay is used.

The possible values first_week_day are:

  • 0 for Monday
  • 1 for Tuesday
  • 2 for Wednesday
  • 3 for Thursday
  • 4 for Friday
  • 5 for Saturday
  • 6 for Sunday

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
weekstart('12/01/2013') Returns 07/01/2013.
weekstart('12/01/2013', -1 ) Returns 31/11/2012.
weekstart('12/01/2013', 0, 1) Returns 08/01/2013.

This example finds the first day of the week following the week of each invoice date in the table.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
WeekStart(InvDate, 1) AS WkStart
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the weekstart() function.
InvDate WkStart
28/03/2012 02/04/2012
10/12/2012 17/12/2012
5/2/2013 11/02/2013
31/3/2013 01/04/2013
19/5/2013 20/05/2013
15/9/2013 16/09/2013
11/12/2013 16/12/2013
2/3/2014 03/03/2014
14/5/2014 19/05/2014
13/6/2014 16/06/2014
7/7/2014 14/07/2014
4/8/2014 11/08/2014

weekyear

This function returns the year to which the week number belongs according to ISO 8601. The week number ranges between 1 and approximately 52.

weekyear(expression)

Return data type: integer

Example Result
weekyear( '1996-12-30' ) returns 1997, because week 1 of 1998 starts on 1996-12-30
weekyear( '1997-01-02' ) returns 1997
weekyear( '1997-12-28' ) returns 1997
weekyear( '1997-12-30' ) returns 1998, because week 1 of 1998 starts on 1997-12-29
weekyear( '1999-01-02' ) returns 1998, because week 53 of 1998 ends on 1999-01-03

Some years, week #1 starts in December, e.g. December 1997. Other years start with week #53 of previous year, e.g. January 1999. For those few days when the week number belongs to another year, the functions year and weekyear will return different values.

year

This function returns an integer representing the year when the expression is interpreted as a date according to the standard number interpretation.

year(expression)

Return data type: integer

Example Result
year( '2012-10-12' ) returns 2012
year( '35648' ) returns 1997, because 35648 = 1997-08-06

yearend

This function returns a value corresponding to a timestamp of the last millisecond of the last day of the year containing date. The default output format will be the DateFormat set in the script.

YearEnd(date[, period_no[, first_month_of_year = 1]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer, where the value 0 indicates the year which contains date. Negative values in period_no indicate preceding years and positive values indicate succeeding years.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
yearend ( '19/10/2001' ) Returns 31/12/2001 23:59:59.
yearend ( '19/10/2001', -1 ) Returns 31/12/2000 23:59:59.
yearend ( '19/10/2001', 0, 4) Returns 31/03/2002 23:59:59.

This example finds the final day in the year of each invoice date in the table, where the first month in the year is specified as month 4.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
YearEnd(InvDate, 0, 4) AS YrEnd
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the yearend() function.
InvDate YrEnd
28/03/2012 31/03/2011
10/12/2012 31/03/2012
5/2/2013 31/03/2013
31/3/2013 31/03/2013
19/5/2013 31/03/2014
15/9/2013 31/03/2014
11/12/2013 31/03/2014
2/3/2014 31/03/2014
14/5/2014 31/03/2015
13/6/2014 31/03/2015
7/7/2014 31/03/2015
4/8/2014 31/03/2015

yearname

This function returns a four-digit year as display value with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the year containing date.

YearName(date[, period_no[, first_month_of_year]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer, where the value 0 indicates the year which contains date. Negative values in period_no indicate preceding years and positive values indicate succeeding years.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year. The display value will then be a string showing two years.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
yearname ( '19/10/2001') Returns 2001.
yearname ( '19/10/2001', -1 ) Returns '2000.
yearname ( '19/10/2001', 0, 4) Returns '2001-2002.

This example creates a four-plus-four digit name for the years in which each invoice date in the table is found. This is because the first month in the year is specified as month 4.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
YearName(InvDate, 0, 4) AS YrName
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the yearname() function.
InvDate YrName
28/03/2012 2011-2012
10/12/2012 2012-2013
5/2/2013 2012-2013
31/3/2013 2012-2013
19/5/2013 2013-2014
15/9/2013 2013-2014
11/12/2013 2013-2014
2/3/2014 2013-2014
14/5/2014 2014-2015
13/6/2014 2014-2015
7/7/2014 2014-2015
4/8/2014 2014-2015

yearstart

This function returns a timestamp corresponding to the start of the first day of the year containing date. The default output format will be the DateFormat set in the script.

YearStart(date[, period_no[, first_month_of_year]])

Return data type: dual

Argument Description
date The date to evaluate.
period_no period_no is an integer, where the value 0 indicates the year which contains date. Negative values in period_no indicate preceding years and positive values indicate succeeding years.
first_month_of_year If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.

Example Result
yearstart ('19/10/2001') Returns 01/01/2001.
yearstart ('19/10/2001', -1) Returns 01/01/2000.
yearstart ('19/10/2001', 0, 4)

Returns 01/04/2001.

This example finds the first day in the year of each invoice date in the table, where the first month in the year is specified as month 4.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
 
InvoiceData:
LOAD *,
YearStart(InvDate, 0, 4) AS YrStart
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the yearstart() function.
InvDate YrStart
28/03/2012 01/04/2011
10/12/2012 01/04/2012
5/2/2013 01/04/2012
31/3/2013 01/04/2012
19/5/2013 01/04/2013
15/9/2013 01/04/2013
11/12/2013 01/04/2013
2/3/2014 01/04/2013
14/5/2014 01/04/2014
13/6/2014 01/04/2014
7/7/2014 01/04/2014
4/8/2014 01/04/2014

yeartodate

This function finds if the input timestamp falls within the year of the date the script was last loaded, and returns True if it does, False if it does not.

YearToDate(timestamp[ , yearoffset [ , firstmonth [ , todaydate]]])

Return data type: Boolean

If none of the optional parameters are used, the year to date means any date within one calendar year from January 1 up to and including the date of the last script execution.

Argument Description
timestamp The timestamp to evaluate, for example '2012-10-12'.
yearoffset By specifying a indicates a previous year, a positive offset a future year. The most recent year-to-date is achieved by specifying yearoffset = -1. If omitted, 0 is assumed.
firstmonth By specifying a firstmonth between 1 and 12 (1 if omitted) the beginning of the year may be moved forward to the first day of any month. For example, if you want to work with a fiscal year beginning on May 1, specify firstmonth = 5.
todaydate By specifying a todaydate (timestamp of the last script execution if omitted) it is possible to move the day used as the upper boundary of the period.

The following examples assume last reload time = 2011-11-18

Example Result
yeartodate( '2010-11-18') returns False
yeartodate( '2011-02-01') returns True
yeartodate( '2011-11-18') returns True
yeartodate( '2011-11-19') returns False
yeartodate( '2011-11-19', 0, 1, '2011-12-31') returns True
yeartodate( '2010-11-18', -1) returns True
yeartodate( '2011-11-18', -1) returns False
yeartodate( '2011-04-30', 0, 5) returns False
yeartodate( '2011-05-01', 0, 5) returns True