Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

ROUND_QUARTER

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

Description

Rounds the date to the beginning of the quarter, i.e. leaving the year unchanged and setting the month and day to the beginning of the quarter and setting hour, minute, second and millisecond to their base value

Syntax
ROUND_QUARTER(column)
NULL handling

If the input value is NULL, then the result is NULL as well.

Examples

[1] Round dates from the beginning of a quarter to the beginning of the quarter.

Query

Column1

ROUND_QUARTER ( "Table1"."Column1" )

Input

Output

Table1

Column1 : DATE

Fri Jan 01 2016 00:00:00.000

Fri Apr 01 1994 00:00:00.000

Sat Jul 01 2017 00:00:00.000

Sun Oct 01 2000 00:00:00.000

null

Result

Column1 : DATE

Fri Jan 01 2016 00:00:00.000

Fri Apr 01 1994 00:00:00.000

Sat Jul 01 2017 00:00:00.000

Sun Oct 01 2000 00:00:00.000

null

[2] Round dates from the middle of a quarter to the beginning of the quarter.

Query

Column1

ROUND_QUARTER ( "Table1"."Column1" )

Input

Output

Table1

Column1 : DATE

Thu Mar 31 2016 23:59:59.999

Thu Jun 30 1994 23:59:59.999

Sat Sep 30 2017 23:59:59.999

Sun Dec 31 2000 23:59:59.999

Result

Column1 : DATE

Fri Jan 01 2016 00:00:00.000

Fri Apr 01 1994 00:00:00.000

Sat Jul 01 2017 00:00:00.000

Sun Oct 01 2000 00:00:00.000

[3] Round dates from the end of a quarter to the beginning of the quarter.

Query

Column1

ROUND_QUARTER ( "Table1"."Column1" )

Input

Output

Table1

Column1 : DATE

Sat Feb 13 2016 12:12:12.555

Fri May 13 1994 12:12:12.555

Sun Aug 13 2017 12:12:12.555

Mon Nov 13 2000 12:12:12.555

Result

Column1 : DATE

Fri Jan 01 2016 00:00:00.000

Fri Apr 01 1994 00:00:00.000

Sat Jul 01 2017 00:00:00.000

Sun Oct 01 2000 00:00:00.000