Teradata: First and Last Day

Teradata: First and Last Day

Teradata provides a way to truncate dates to the start of the week, month, quarter and year. This can achieved using inbuilt TRUNC function.

Syntax: TRUNC(date_column, Trunc_keyword) 


Below tables show most of the metacharacters that can used for Trunc_keyword.

Trunc_keyword

Description

Y

Truncate date to start of the year (Example: 2018-09-10 to 2018-01-01)

YEAR

Truncate date to start of the year (Example: 2018-09-10 to 2018-01-01)

Q

Truncate day to start of the Quarter (Example: 2018-09-10 to 2018-07-01)

RM

Truncate day to start of the month (Example: 2018-09-10 to 2018-09-01)

MON

Truncate day to start of the month (Example: 2018-09-10 to 2018-09-01)

MONTH

Truncate day to start of the month (Example: 2018-09-10 to 2018-09-01)

DAY

Truncate day to start of the week

D

Truncate day to start of the week

W

Truncate date to day of the week which is same as first day of month

WW

Truncate date to day of the week which is same as first day of year



All the below examples can be used with any DATE column (i.e CURRENT_DATE can be replaced with column of DATE datatype).