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”
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.