Sysdate in Teradata

There is no sysdate built-in function in Teradata. You can use current_date and current_timestamp function.You can also create custom function named "sysdate" to replicate it in Teradata. If you have worked in Oracle or MySQL then you must have used sysdate a lot. In this post we will see how you can use "sysdate" in Teradata too.

What is SYSDATE ?

Sysdate returns current date or timestamp of database server. Also it may accept optional date or time format for output result. SYSDATE is primarily used to get current date , current timestamp or current time.

Current Date in Teradata

You can use current_date function to get current date value.
Also you can apply CAST on current date to return output in desired formats.

select current_date as sysdate , cast(current_date as date format 'dd-MMM-yyyy') as newdate;

sysdate          newdate
----------      --------------
2021-10-25       25-Oct-2021

You can read this post about different DATE formats available in Teradata with examples

Current Timestamp in Teradata

You can use current_timestamp function to get current timestamp value in teradata.
Also you can apply CAST on current timestamp to return output in different formats.

select current_timestamp as sysdate , cast(current_timestamp as timestamp format 'dd-MMM-yyyyBHH:MI:SSBT') as newtimestamp;

sysdate                   newtimestamp
-------------------     ------------------------
2021-10-25 11:34:53       25-Oct-2021 11:34:53 AM

You can read this post about different TIMESTAMP formats available in Teradata.

Current Time in Teradata

You can use current_time function to get current time value in teradata.
Also you can apply CAST on current time to return output in different formats.

select current_time as sysdate , cast(current_time as TIME format 'HH:MI:SSBT') as newtime;

sysdate                   newtime
-------------------     ------------------------
11:34:53                  11:34:53 AM

How to use sysdate in Teradata ?

You can create a custom function in Teradata with name sysdate. Then you can use sysdate in teradata just like you use in other databases. I will suggest to use this approach only when you must use sysdate and not current_date or current_timestamp. One situation could be that you want same query to run on MySQL/Oracle/Teradata.

CREATE FUNCTION SYSDATE()
   RETURNS DATE
   LANGUAGE SQL
   CONTAINS SQL
   DETERMINISTIC
   SQL SECURITY DEFINER
   COLLATION INVOKER
   INLINE TYPE 1
   RETURN CAST(CURRENT_DATE AS DATE FORMAT 'DD-MM-YYYY');

Please modify the return statement to replicate same output as you want from sysdate.

One thought on “Sysdate in Teradata

  1. I don't think anyone needs this kind of sysdate function. Each database supports different functions, but all database has to agree for ANSI SQL.
    So current_date is ANSI SQL and it is supported by all the databases including Oracle.
    Moreover, sysdate in Oracle gives HH:MM:SS also including the date. So based on the above two reasons, there is no point in creating sysdate in Teradata.

Leave a Reply

Your email address will not be published. Required fields are marked *