Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 

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 End_time, QIDDESCRIPTION(qid) AS 'Event Name' FROM events

Returns start time, end 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