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.
Letter |
Date or time parameter |
Presentation |
Examples |
|---|---|---|---|
y |
Calendar year |
Year Date example used is: 20-June-2016 |
Returns date format: 16-06-20
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 |
Returns date format: 16-06-20
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 |
Returns date format: 2016-June-20
Returns date format: 2016-Jun-20
Returns date format: 2016-06-20 |
w |
Week in year |
Number Date example used is: 20-June-2016 |
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 |
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 |
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 |
Returns date format: 2016-06-20 |
F |
Day of week in month |
Number Date example used is: 20-June-2016 |
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 |
Returns date format: 2016-06-Mon |
a |
AM or PM |
Text Date example used is: 20-June-2016 |
2016-06-20 06 PM |
H |
Hour in day (0-23) |
Number Date example used is: 20-June-2016 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Returns date format: 2016-06-20 6:10 PM + 01 Note:
colon added in query to format time |