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)
6 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)

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

Thank you.  I am also trying to get to a MM/DD/YYYY 00:00:00 format as well.  But I think my challenge is a bit different and likely needs additional formating instructions.  Here is the scenario:

 

I have a Teradata view that produces results sorted by week ending (Fridays) that I need to join with a MS Access table that sorts its data also by week ending.  So, my left join will be keyed on the week ending date from each data source.  My challenge is the MS Access date field is stored as date / time.  In my MS Access source data, I have the correct MM/DD/YYYY dates and each has "00:00:00" as its time value.  So, in order for me to properly join the two tables, I need to get my Teradata view to result to produce a timestamp value where the date is the correct Friday (I have this result now) and the time portion is all 0's, that is "00:00:00".

 

Thanks for any direction and help you can provide.

 

Teradata Employee

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

cast( <the-date-from-Teradata> as timestamp(0) format'mm/dd/yyyybhh:mi:ss' )