String To Date Conversion (Date CASTing)

Converting Strings to dates

Normally we have scenarios where we have dates in character string format and we need to cast them to date data type to perform date operations.

If we are working with character literals then we can use following syntax to convert them to date.

SELECT DATE '2013-04-07';   Adding the word DATE ahead of the string tells Teradata to interpret the string as Date. When using this syntax, we should make sure that the characters literal is in the format of 'YYYY-MM-DD'. This ANSI standard syntax.

We can use explicit cast syntax as shown below :

1) SELECT CAST ( '2013-03-03' AS DATE );

2) SELECT CAST ( '2013/03/03' AS DATE );

However if we try to do a cast as below the query fails.

3) SELECT CAST ( '20130303' AS DATE );

Why does this query fail? OR rather the question should be why did the earlier two queries run?

The reason is in query number 1 the string is of format 'YYYY-MM-DD' and as Teradata supports ANSI, it can interpret this string.

The query number 2 the string is of format 'YYYY/MM/DD' which is traditional Teradata date format and hence it could interpret this string too.

However in the 3rd query the string is not in any standard date format and hence Teradata could not interpret it.

To make the 3rd query work we need to use the following

SELECT CAST ( '20130303' AS DATE FORMAT 'YYYYMMDD');

Now it works because we provided the format that could be used to interpreting the string value.

Note that FORMAT is not within separate brackets and also there is no comma between DATE and FORMAT.

We can also use Teradata implicit cast for performing the conversion.

Teradata had its own way of casting things before ANSI standard CAST was incorporated. Teradata's earlier syntax was called implicit cast.

Following are how implicit cast works:

1) SELECT  '2013-04-04' (DATE) ;

2) SELECT  '2013/04/04' (DATE) ;

Reason only there two date formats are acceptable because one is ANSI standard and other is Teradata standard.

To convert strings that are not in any of the able formats we use below syntax.

3) SELECT  '20130404' (DATE, FORMAT 'YYYYMMDD');

Note that in this syntax we need to provide the format to be used for interpretation.

Also note that here we have a comma between words DATE and FORMAT.

Some times we would want the converted date to be displayed in a different format.

Example: We have a string of format 'YYYYMMDD' and we display it in the format 'YY/MM/DD'

Following is the query that would give us this result.

SELECT CAST (

CAST ( '20130303' AS DATE FORMAT 'YYYYMMDD')

AS DATE FORMAT 'YY/MM/DD');

The inner cast first converts the string of the format YYYYMMDD to date data type.

The FORMAT clause on the inner CAST indicates the format to be used to interpreting the string.

Once the string is converted to Date we use another outer cast to convert the format.

Note that we can use CAST to change formats also.                        

Caution: When  you run this is your SQL assistant we would not find the result we expected.

The reason behind this is that SQL assistant is a ODBC tool and does not use CLI.

Hence it displays date it the out format as decided by the settings.

This will work perfectly fine if we run it using BTEQ.

To make it run under SQL assistant we use following syntax. We add another CAST to convert the result to character as shown below

SELECT CAST (CAST ( CAST ( '20130303' AS DATE FORMAT 'YYYYMMDD') AS DATE FORMAT 'YY/MM/DD') AS CHAR(08));