GETDATE

Date function

Description

GETDATE returns the current date and time in the current session time zone (UTC by default). It returns the start date or time of the current statement, even when it is within a transaction block. (Redshift SQL Language Reference GETDATE function).

In Snowflake this function provides the current date and time with nanosecond precision (up to 9 digits) and is timezone-aware.

This function is fully supported in Snowflake.

For more information about quoted identifiers in functions, click here.

Grammar Syntax

GETDATE()

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE table1 (
    id INTEGER,
    date_t DATE DEFAULT getdate(),
    time_t TIME DEFAULT  "getdate"(),
    timestamp_t TIMESTAMP DEFAULT "GETDATE"(),
    timestamptz_t TIMESTAMPTZ DEFAULT getdate()
);

INSERT INTO table1(id) VALUES (1);

SELECT * FROM table1;

Result

ID	DATE_T	TIME_T	TIMESTAMP_T	TIMESTAMPTZ_T
1	2024-11-20	17:51:00	2024-11-20 17:51:00.000000	2024-11-20 17:51:00.000000 +00:00

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE table1 (
    id INTEGER,
    date_t DATE DEFAULT getdate() :: DATE,
    time_t TIME DEFAULT GETDATE() :: TIME,
    timestamp_t TIMESTAMP DEFAULT GETDATE(),
    timestamptz_t TIMESTAMP_TZ DEFAULT getdate()
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/20/2024",  "domain": "test" }}';

INSERT INTO table1 (id) VALUES (1);

SELECT * FROM
    table1;

Result

ID	DATE_T	TIME_T	TIMESTAMP_T	TIMESTAMPTZ_T
1	2024-11-20	17:51:00	2024-11-20 17:51:00.000	2024-11-20 17:51:00.000 +0000

Known Issues

In Amazon Redshift, the default precision for timestamps is 6 digits (microseconds), while in Snowflake, the default precision is 9 digits (nanoseconds). Due to these differences in precision, it’s important to consider your specific needs when working with timestamps. If you require different precision in either platform, you can use the following options.

1. Use ALTER SESSION:

   --This example is for 2 digits for precision (FF2).
   ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF2';

Please note that depending on the data type used to store the value obtained with GETDATE(), there may be limitations in precision that could result in a loss of accuracy.

Related EWIs

There are no known issues.