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: |
|
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:
|
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.
|
|
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: |
|
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: |
|
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: |
|
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: |
|
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:
|
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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;
|
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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:
|
|
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: |
|
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: |
|
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: |
|
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' |
||||||||||||||||||||||||||||
SetYear:
|
|
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:
|
|
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) |
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:
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:
The alternative is to use broken weeks.
The following values can be used:
|
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:
|
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:
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:
|
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: |
|
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_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:
|
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: |
|
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:
|
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: |
|
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: |
|
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: |
|
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: |
|
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 |