Date and time functions
Qlik Sense date and time functions are used to transform and convert date and time values. All functions can be used in both the data load script and in chart expressions.
Functions 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.
Qlik Sense 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 Qlik Sense attempts 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, Qlik Sense 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.
When processing a timestamp with a date or time function, Qlik Sense ignores any daylight savings time parameters unless the date or time function includes a geographical 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.
Use the drop-down on each function to see a brief description and the syntax of each function. Click the function name in the syntax description for further details.
Integer expressions of time
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)
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)
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)
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)
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(expression)
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)
This function returns an integer representing the year when the expression is interpreted as a date according to the standard number interpretation.
year(expression)
This function returns the year to which the week number belongs according to the environment variables. The week number ranges between 1 and approximately 52.
weekyear(expression)
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)
Timestamp functions
This function returns a timestamp of the current time. The function returns values in the TimeStamp system variable format. The default timer_mode value is 1.
now([ timer_mode])
This function returns the current date. The function returns values in the DateFormat system variable format.
today([timer_mode])
This function returns a timestamp of the current time for a specified time zone.
localtime([timezone [, ignoreDST ]])
Make functions
This function returns a date calculated from the year YYYY, the month MM and the day DD.
makedate(YYYY [ , MM [ , DD ] ])
This function returns a date calculated from the year, the week number, and the day of week .
makeweekdate(YYYY [ , WW [ , D ] ])
This function returns a time calculated from the hour hh, the minute mm, and the second ss.
maketime(hh [ , mm [ , ss [ .fff ] ] ])
Other date functions
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])
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)
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(date [ , yearoffset [ , firstmonth [ , todaydate] ] ])
Timezone functions
This function returns the time zone, as defined on the computer where the Qlik engine is running.
timezone( )
This function returns the current Greenwich Mean Time, as derived from the regional settings.
GMT( )
Returns the current Coordinated Universal Time.
UTC( )
Returns the current adjustment for daylight saving time, as defined in Windows.
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]])
Set time functions
This function takes as input a timestamp and a year and updates the timestamp with the year specified in input.
setdateyear (timestamp, year)
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)
In... functions
This function returns True if timestamp lies inside the year containing base_date.
inyear(date, basedate , shift [, first_month_of_year = 1])
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 (date, basedate , shift [, first_month_of_year = 1])
This function returns True if timestamp lies inside the quarter containing base_date.
inquarter (date, basedate , shift [, first_month_of_year = 1])
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 (date, basedate , shift [, first_month_of_year = 1])
This function returns True if timestamp lies inside the month containing base_date.
inmonth(date, basedate , shift)
Returns True if date lies inside the part of month containing basedate up until and including the last millisecond of basedate.
inmonthtodate (date, basedate , shift)
This function finds if a timestamp falls within the same month, bi-month, quarter, four-month period, 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, date, basedate , shift [, first_month_of_year = 1])
This function finds if a timestamp falls within the part a period of the month, bi-month, quarter, four-month period, 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.
inmonthstodate (n, date, basedate , shift [, first_month_of_year = 1])
This function returns True if timestamp lies inside the week containing base_date.
inweek(date, basedate , shift [, weekstart])
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 (date, basedate , shift [, weekstart])
This function determines if timestamp lies inside the lunar week containing base_date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week., Apart from the final week of the year, each week will contain exactly seven days.
inlunarweek (date, basedate , shift [, weekstart])
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 in Qlik Sense are defined by counting January 1 as the first day of the week and, apart from the final week of the year, will contain exactly seven days.
inlunarweektodate (date, basedate , shift [, weekstart])
This function returns True if timestamp lies inside the day containing base_timestamp.
inday(timestamp, basetimestamp , shift [, daystart])
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, basetimestamp , shift [, daystart])
Start ... end functions
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 [, shift = 0 [, first_month_of_year = 1]])
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 [, shift = 0 [, first_month_of_year = 1]])
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 [, shift = 0 [, first_month_of_year = 1]] )
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 [, shift = 0 [, first_month_of_year = 1]])
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 [, shift = 0 [, first_month_of_year = 1]])
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 [, shift = 0 [, first_month_of_year = 1]])
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 [, shift = 0])
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 [, shift = 0])
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 [, shift = 0])
This function returns a value corresponding to the timestamp of the first millisecond of the month, bi-month, quarter, four-month period, or half-year containing a base date. It is also possible to find the timestamp for a previous or following time period.The default output format is the DateFormat set in the script.
monthsstart(n, date [, shift = 0 [, first_month_of_year = 1]])
This function returns a value corresponding to a timestamp of the last millisecond of the month, bi-month, quarter, four-month period, or half-year containing a base date. It is also possible to find the timestamp for a previous or following time period.
monthsend(n, date [, shift = 0 [, first_month_of_year = 1]])
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, four-month period, or half-year containing a base date.
monthsname(n, date [, shift = 0 [, first_month_of_year = 1]])
This function returns a value corresponding to a timestamp of the first millisecond of the first day of the calendar week containing date. The default output format is the DateFormat set in the script.
weekstart(date [, shift = 0 [,weekoffset = 0]])
This function returns a value corresponding to a timestamp of the last millisecond of the last day of the calendar week containing date. The default output format will be the DateFormat set in the script.
weekend(date [, shift = 0 [,weekoffset = 0]])
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 [, shift = 0 [,weekoffset = 0]])
This function returns a value corresponding to a timestamp of the first millisecond of the first day of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week and, apart from the final week of the year, will contain exactly seven days.
lunarweekstart(date [, shift = 0 [,weekoffset = 0]])
This function returns a value corresponding to a timestamp of the last millisecond of the last day of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week and, apart from the final week of the year, will contain exactly seven days.
lunarweekend(date [, shift = 0 [,weekoffset = 0]])
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 in Qlik Sense are defined by counting January 1as the first day of the week and, apart from the final week of the year, will contain exactly seven days.
lunarweekname(date [, shift = 0 [,weekoffset = 0]])
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(timestamp [, shift = 0 [, dayoffset = 0]])
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(timestamp [, shift = 0 [, dayoffset = 0]])
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(timestamp [, shift = 0 [, dayoffset = 0]])
Day numbering functions
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)
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})
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 or timestamps.
firstworkdate(end_date, no_of_workdays {, holiday} )
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 holiday. start_date and holiday should be valid dates or timestamps.
lastworkdate(start_date, no_of_workdays {, holiday})
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(date[,firstmonth])
This function calculates the day number of the quarter in which a timestamp falls. This function is used when creating a Master Calendar.
daynumberofquarter(date[,firstmonth])