How to Mimic Oracle’s SYSDATE function in Teradata

There is no direct counterpart of Oracle’s SYSDATE function in Teradata. There is CURRENT_DATE and DATE(both return date only), CURRENT_TIME (time only), CURRENT_TIMESTAMP (similar to SYSTIMESTAMP in Oracle), but in Order to mimic SYSDATE you need to concatenate results of two functions:

SELECT CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0))

or

SELECT CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY-MM-DD') || ' ' || CAST(CAST(CURRENT_TIME AS FORMAT 'HH:MI:SS') AS CHAR(8)) AS TIMESTAMP(0))

The following function will encapsulate the above expression and give you a feeling of using Oracle’s SYSDATE function:

REPLACE FUNCTION SYSDATE()
   RETURNS TIMESTAMP
   LANGUAGE SQL
   CONTAINS SQL
   DETERMINISTIC
   SQL SECURITY DEFINER
   COLLATION INVOKER
   INLINE TYPE 1
   RETURN CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0));

Now, you can use it as follows:

SELECT SYSDATE()

Result:
03/24/2016 12:25:23
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s