Get the date and time in yyyy-mm-dd 00:00:00 format in TERADATA SQL

Database
Enthusiast

Get the date and time in yyyy-mm-dd 00:00:00 format in TERADATA SQL

Hi All ,

I m new to teradata so i m not able to get the date in YYYY-MM-DD 00:00:00 format in TERADATA.

Can some one please help me ...

Thanks in Advancce

GTREDDY

Tags (1)
4 REPLIES
Junior Supporter

Re: Get the date and time in yyyy-mm-dd 00:00:00 format in TERADATA SQL

Hi.

In Teradata Dates are Dates, Times are Times and Timestamps are Timestamps.

This is not Oracle.

Cheers.

Carlos.

Teradata Employee

Re: Get the date and time in yyyy-mm-dd 00:00:00 format in TERADATA SQL

@GTREDDY, you didn't say what tool you are using. For example, are you using BTEQ?

If you are getting an output date/time format that you need to change, then you need to cast the value twice -- the first cast with an "as format" clause, and the second cast to VARCHAR.

Also, as Carlos noted, DATE values, TIME values, and TIMESTAMP values are all distinct from each other. So if you want to display a DATE in a TIMESTAMP format, you must first cast the DATE to be a TIMESTAMP. And if you want to display a TIME in a TIMESTAMP format, you must first case the TIME to be a TIMESTAMP.

You need 2 or 3 nested casts, depending on the original data type.

Here are examples:

select cast(cast(cast(current_date as timestamp) as format 'YYYY-MM-DDBHH:MI:SS') as varchar(100));

select cast(cast(cast(current_time as timestamp) as format 'YYYY-MM-DDBHH:MI:SS') as varchar(100));

select cast(cast(current_timestamp as format 'YYYY-MM-DDBHH:MI:SS') as varchar(100));

Enthusiast

Re: Get the date and time in yyyy-mm-dd 00:00:00 format in TERADATA SQL

Hi All,

I have scheduled a job which will extract the data from teradata server on every day.So i have to pass current date with time as i metioned in my post.

Thanks 

GTREddy

Senior Apprentice

Re: Get the date and time in yyyy-mm-dd 00:00:00 format in TERADATA SQL

As Tom Nolan already wrote:

cast(current_date as timestamp)