AQL date and time formats

Use Ariel Query Language (AQL) date and time formats to represent times and dates in queries.

The following table lists the letters that represent date and time in AQL queries. This table is based on the SimpleDateFormat.

Table 1. Date and time formats
Letter Date or time parameter Presentation Examples
y Calendar year Year

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yy-MM-dd')

Returns date format: 16-06-20

DATEFORMAT(starttime,'yyyy-MM-dd')

Returns date format: 2016-06-20

SELECT 
DATEFORMAT(devicetime,'yyyy-MM-dd') 
AS Log_Src_Date, QIDDESCRIPTION(qid) 
AS 'Event Name' 
FROM events
Y Week year Year

The first and last days of a week year can have different calendar year values.

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'YY-MM-dd')

Returns date format: 16-06-20

DATEFORMAT(starttime,'YYYY-MM-dd')

Returns date format: 2016-06-20

SELECT 
DATEFORMAT(starttime,'YYYY-MM-dd hh:mm') 
AS 'Start Time', 
DATEFORMAT(endtime,'YYYY-MM-dd hh:mm') 
AS Storage_time, 
QIDDESCRIPTION(qid) 
AS 'Event Name' 
FROM events

Returns start time, storage time, and event name columns
M Month in year Month

3 or more letters are interpreted as text.

2 letters are interpreted as a number.

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-MMMM-dd')

Returns date format: 2016-June-20

DATEFORMAT(starttime,'yyyy-MMM-dd')

Returns date format: 2016-Jun-20

DATEFORMAT(starttime,'yyyy-MM-dd')

Returns date format: 2016-06-20
w Week in year Number

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-ww-dd')

Returns date format: 2016-26-20

Note: 26 is week 26 in year
W Week in month Number

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-WW-dd')

Returns date format: 2016-04-20

Note: 04 is week 4 in month
D Day in year Number

Day in year represented by number

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-mm-DD')

Returns date format: 2016-06-172

Note: 172 is day number 172 in year
d Day in month Number

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-mm-dd')

Returns date format: 2016-06-20
F Day of week in month Number

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-MM-FF')

Returns date format: 2016-06-03

Note: 03 is day 3 of week in month
E Day name in week Text

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-MM-EE')

Returns date format: 2016-06-Mon
a AM or PM Text

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-MM-dd h a')

2016-06-20 06 PM
H Hour in day (0-23) Number

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-MM-dd H')

Returns date format: 2016-06-20 18

Note: 18 is 18:00 hours
k Hour in day (1-24) Number

Date example used is: 20-June-2016

 DATEFORMAT(starttime,'yyyy-MM-dd k')

Returns date format: 2016-06-20 18

Note: 18 is 18:00 hours
K Hour in AM/PM (0-11) Number

Date example used is: 20-June-2016, 6 PM

 DATEFORMAT(starttime,'yyyy-MM-dd K a')

Returns date format: 2016-06-20 6 PM

Note: K = 6 and a = PM
h Hour in AM/PM (1-12) Number

Date example used is: 20-June-2016 6 PM

 DATEFORMAT (starttime,'yyyy-MM-dd h a')

Returns date format: 2016-06-20 6 PM

Note: h = 6 and a = PM
m Minute in hour Number

Date example used is: 20-June-2016, 6:10 PM

 DATEFORMAT(starttime,'yyyy-MM-dd h:m a')

Returns date format: 2016-06-20 6:10 PM

Note: colon added in query to format time
s Second in minute Number

Date example used is: 20-June-2016, 6:10:56 PM

 DATEFORMAT(starttime,'yyyy-MM-dd h:m:s a')

Returns date format: 2016-06-20 6:10:56 PM

Note: colons added in query to format time
S Millisecond Number

Date example used is: 20-June-2016, 6:10 PM

 DATEFORMAT(starttime,'yyyy-MM-dd h:m:ss:SSS a')

Returns date format: 2016-06-20 6:10:00:322 PM

Note: colons added in query to format time
z Time zone General Time zone

Date example used is: 20-June-2016, 6:10 PM GMT +1

 DATEFORMAT(starttime,'yyyy-MM-dd h:m a z')

Returns date format: 2016-06-20 6:10 PM GMT + 1

Note: colon added in query to format time
Z Time zone RFC 822 time zone

Date example used is: 20-June-2016, 6:10 PM GMT +1

 DATEFORMAT(starttime,'yyyy-MM-dd h:m a Z')

Returns date format: 2016-06-20 6:10 PM + 0100

Note: colon added in query to format time
X Time zone ISO 8601 time zone

Date example used is: 20-June-2016, 6:10 PM GMT +1

 DATEFORMAT(starttime,'yyyy-MM-dd h:m a X')

Returns date format: 2016-06-20 6:10 PM + 01

Note: colon added in query to format time