TRUNC

Date and Time Function

Description

Truncates a TIMESTAMP and returns a DATE.

For more information, please refer to TRUNC function.

Grammar Syntax

TRUNC(timestamp)

This function is supported by Snowflake. However in Snowflake it truncates a DATE, TIME or TIMESTAMP value to the specified precision.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE test_date_trunc (
    mytimestamp TIMESTAMP,
    mydate DATE,
    mytimestamptz TIMESTAMPTZ
 );

INSERT INTO test_date_trunc VALUES (
  '2024-05-09 08:50:57.891 -0700',
  '2024-05-09',
  '2024-05-09 08:50:57.891 -0700');

SELECT TRUNC(mytimestamp) AS date1,
       TRUNC(mydate) AS date2,
       TRUNC(mytimestamptz::TIMESTAMP) AS date3,
       TRUNC('2024-05-09 08:50:57.891 -0700'::TIMESTAMP) AS date4,
       TRUNC('2024-05-09 08:50:57.891 -0700'::DATE) AS date5,
       CAST(TRUNC('2024-05-09 08:50:57.891 -0700'::TIMESTAMP) AS TIMESTAMP) AS date6
  FROM test_date_trunc;

Results

DATE1	DATE2	DATE3	DATE4	DATE5	DATE6
2024-05-09	2024-05-09	2024-05-09	2024-05-09	2024-05-09	2024-05-09 00:00:00.000

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE test_date_trunc (
    mytimestamp TIMESTAMP,
    mydate DATE,
    mytimestamptz TIMESTAMP_TZ
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/13/2024",  "domain": "test" }}';

INSERT INTO test_date_trunc
VALUES (
  '2024-05-09 08:50:57.891 -0700',
  '2024-05-09',
  '2024-05-09 08:50:57.891 -0700');

SELECT
    DATE( TRUNC(mytimestamp, 'day')) AS date1,
    DATE(
    TRUNC(mydate, 'day')) AS date2,
    DATE(
    TRUNC(mytimestamptz::TIMESTAMP, 'day')) AS date3,
    DATE(
    TRUNC('2024-05-09 08:50:57.891 -0700'::TIMESTAMP, 'day')) AS date4,
    DATE(
    TRUNC('2024-05-09 08:50:57.891 -0700'::DATE, 'day')) AS date5,
    CAST(DATE(TRUNC('2024-05-09 08:50:57.891 -0700'::TIMESTAMP, 'day')) AS TIMESTAMP) AS date6
FROM
    test_date_trunc;

Results

DATE1	DATE2	DATE3	DATE4	DATE5	DATE6
2024-05-09	2024-05-09	2024-05-09	2024-05-09	2024-05-09	2024-05-09 00:00:00.000

Related EWIs

There are no known issues.