All Forums Database
jehan 12 posts Joined 11/08
11 Nov 2008
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 formatsecond query Convert 14/July/1974 into 14-07-1974 formatthird query Convert 4 Jul 1976 into 14-7-1974 formatlike Select cast('04 August 1983' as date format 'BDDBM4BY4');but problem in case of following querySelect cast('4 August 1983' as date format 'BDDBM4BY4');also problem in case of following querySelect cast('4 Aug 1983' as date format 'BDDBM4BY4');also problem in case of following querySelect cast('04/August/1983' as date format 'BDDBM4BY4');also problem in case of following querySelect cast('04/08/1983' as date format 'BDDBM4BY4');

Adeel Chaudhry 773 posts Joined 04/08
12 Nov 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

Fred 1096 posts Joined 08/04
12 Nov 2008

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.

i5commuter 10 posts Joined 01/12
24 Feb 2012

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?

You must sign in to leave a comment.