Convert string 13/10/1974 into 13-10-1974 format

Database
Enthusiast

Convert string 13/10/1974 into 13-10-1974 format

i need three seperate queries,
in which

first query
Convert 13/10/1974 into 13-10-1974 format

second query
Convert 14/July/1974 into 14-07-1974 format

third query
Convert 4 Jul 1976 into 14-7-1974 format

like
Select cast('04 August 1983' as date format 'BDDBM4BY4');

but problem in case of following query
Select cast('4 August 1983' as date format 'BDDBM4BY4');

also problem in case of following query
Select cast('4 Aug 1983' as date format 'BDDBM4BY4');

also problem in case of following query
Select cast('04/August/1983' as date format 'BDDBM4BY4');

also problem in case of following query
Select cast('04/08/1983' as date format 'BDDBM4BY4');

3 REPLIES
Teradata Employee

Re: Convert string 13/10/1974 into 13-10-1974 format

Hello,

Following examples can be used to solve most of the issues you mentioned:

SELECT CAST('13/10/1974' AS DATE FORMAT'DD-MM-YYYY');
SELECT CAST(CAST('14/July/1974' AS DATE FORMAT'DD/MMMM/YYYY') AS DATE FORMAT'DD-MM-YYYY');
SELECT CAST(CAST('14 Jul 1974' AS DATE FORMAT'DDBMMMBYYYY') AS DATE FORMAT'DD-MM-YYYY');

And couple of important points, first, running them in BTEQ will give you desired results, Teradata SQL Assistant uses ODBC, and ODBC doesn't support FORMAT command. Second, single digit fields are not supported in date in Teradata as '4/August/1974' it should be '04/August/1974'.

Regards,

Adeel
Teradata Employee

Re: Convert string 13/10/1974 into 13-10-1974 format

I would have phrased it differently:

BTEQ uses FIELD mode by default, which effectively causes an implicit CAST to VARCHAR using the FORMAT. But most client tools and drivers use RECORD mode, in which case you would need to add an explicit CAST to [VAR]CHAR to have the FORMAT applied to a field before the results are returned to the client.
Enthusiast

Re: Convert string 13/10/1974 into 13-10-1974 format

Using syntax from manual does not fix my date conversion issue.

Source data - 2/29/2012

Source column field type is varchar(10)

Target column field type is date

Action needed -  move varchar value of 2/29/2012 to date field.

Conversion being used : cast(stg.Imp_Date AS DATE FORMAT 'MM/DD/YYYY')

I also tried cast(stg.Imp_Date AS DATE FORMAT 'MM-DD-YYYY'), as suggested by another person. Same problem.

Why do we get an error about invalidate date format?

From documentation:

Forcing a FORMAT on CAST for Converting Character to DATE

You can use a FORMAT phrase to convert a character string that does not match the format of

the target DATE data type. A character string in a conversion that does not specify a FORMAT

phrase uses the output format for the DATE data type.

For example, suppose the session dateform is INTEGERDATE and the default DATE format of

the system is set to 'yyyymmdd' through the tdlocaledef utility. The following statement fails,

because the character string contains separators, which does not match the default DATE

format:

SELECT CAST ('2005-01-01' AS DATE);

To override the default DATE format, and convert a character string that contains separators,

specify a FORMAT phrase for the DATE target type:

SELECT CAST ('2005-01-01' AS DATE FORMAT 'YYYY-MM-DD');

What is the format used for? Is it used to parse the input, or format the output for the target of the update?