Internal Date format of Teradata

Database

Internal Date format of Teradata

Hi Team,

 

I am trying to create a table with a Date column in it. I didnt specify any FORMAT to the date column. But, when i look at the DDL using SHOW TABLE, I see that the FORMAT 'YYYY-MM-DD' clause has been added implicitly. I checked the 'Default Date Format' using Help Session, and see it is specified as 'YY/MM/DD'. Is there a reason on why Teradata implicitly FORMAT the date field as 'YYYY-MM-DD' ?

 

Thanks,

Sundar

  • Date Format
  • YYYY-MM-DD

Accepted Solutions
N/A

Re: Internal Date format of Teradata

This is copied from the SQL Assistant  help:

 

When Connected to a Teradata .NET Data Source 

The following list details what occurs when a connection is established with a Teradata .NET data source: 

Teradata.NET always uses ANSI date mode.
All date literals must be entered in 'YYYY-MM-DD' format.

 

 

1 ACCEPTED SOLUTION
8 REPLIES

Re: Internal Date format of Teradata

Hi,

 

This may well be down to the tool/api that you used when creating the table.

 

I ran the following command:

CREATE SET VOLATILE TABLE vt1
(col1 DATE);

Like you, when I run "HELP SESSION" the default date format is "yy/mm/dd".

 

Here are the results that I got when using different tool/api combinations:

BTEQ (cli): format is YY/MM/DD

SQLA (odbc): format is YY/MM/DD - ODBC DSN date/time format is 'III'

SQLA (odbc): format is YY/MM/DD - ODBC DSN date/time format is 'AAA'

SQLA (dot net): format is 'YYYY-MM-DD'

TDStudio:- format is 'YY/MM/DD'

 

Remember that the FORMAt clause only affects display and not storage (which is always the same), although in some circumstances it can affect processing.

 

I would suggest that if you want a specific display format then code it in the CREATE TABLE statement.

 

Does that help?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
N/A

Re: Internal Date format of Teradata

.NET automatically switches to ANSI date, but you can control it on a session level using

SET SESSION DATEFORM = INTEGERDATE; -- or ANSIDATE

Re: Internal Date format of Teradata

Thanks Dave for the response. I am using Teradata SQL Assistant tool and use ODBC for connectivity.

One question that I have, after seeing your response, is that: SQLA (dot net) & SQLA (ODBC). Are there two ways to connect to Teradata using TD SQL Assistant ? How do i find which of these am i using to connect ?

 

I thought .NET framework is used internally by Teradata SQL Assistant for it working. Can you please clarify ?

 

Thanks,

Sundar

Re: Internal Date format of Teradata

Thanks Dnoeth. My Help Session says "Transaction Semantics" as Teradata and 'CurrentDateForm' is already IntegerDate.

 

I thought .NET is just the underlying framework which is used by TD Assistant tool for its working. I would like to know how .NET is associated with Date format of a Teradata DDL ?

 

Thanks,

Sundar

N/A

Re: Internal Date format of Teradata

This is copied from the SQL Assistant  help:

 

When Connected to a Teradata .NET Data Source 

The following list details what occurs when a connection is established with a Teradata .NET data source: 

Teradata.NET always uses ANSI date mode.
All date literals must be entered in 'YYYY-MM-DD' format.

 

 

Re: Internal Date format of Teradata

Thanks for the quick response and throwing knowledge on the .NET connector. In my case, I am using only ODBC connection to Teradata and default date format is also specified as 'YY/MM/DD'. I am not sure why in my DDL then i get a Date format of 'YYYY-MM-DD'. Can you please help me understand this behavior.

 

Thanks,

Sundar

N/A

Re: Internal Date format of Teradata

I don't know if this might be caused by switching dbscontrol general field 15: DateForm to ANSIDate as system default.

 

Re: Internal Date format of Teradata

Thanks Dnoeth & David for all the explanations. Appreciate the efforts.

 

Cheers,

Sundar