Convert string into date

Database
Enthusiast

Convert string into date

Hi ,

Could you  please suggest me how to convert the string value '4/6/2013' to date,

string value coming in different formats

4/6/2013

12/6/2013

6/12/2013

14 REPLIES
Enthusiast

Re: Convert string into date

You may try to cast the string value to date format...

cast (string_value as date format 'YYYY/MM/DD')

Since you have 4/6/2013 you could concatenate a zero (0) to the first character...and then apply cast...

Regards

Re: Convert string into date

Hi ,

How to convert string to Date :

String 'DD-MON-YY' To Date 'YYYY-MM-DD'

ex: 28-jan-13 to convert to date as '2013-01-28'

Can any one help me to convert this..

Thanks in advance....

Regards,

Bhaskar.

Enthusiast

Re: Convert string into date

Hi,

Try this code:

SELECT CAST( CAST( DATE_COL AS DATE FORMAT 'DD-MMM-YY') AS DATE FORMAT 'YYYY-MM-DD');
Khurram
Enthusiast

Re: Convert string into date

Hi Khurram,

my Table is having the date filed format YYYY-MM-DD

need the output to be in this format --- MMMM-DD-YY

Tried both

SELECT  CAST( CAST( DATE_COL AS DATE FORMAT 'YYYY-MM-DD') AS DATE FORMAT 'DD-MMM-YY' )

FROM DBC.TABLE;

SELECT CAST( CAST( DATE_COL AS DATE FORMAT 'DD-MMM-YY') AS DATE FORMAT 'YYYY-MM-DD')

FROM DBC.TABLE;

getting the output as YYYY-MM-DD

how to correct this?

Senior Apprentice

Re: Convert string into date

Hi Rakesh,

a FORMAT is only applied when the column is CASTed to a string:

CAST(CAST(date_col AS FORMAT 'mmmm-dd-yy') AS VARCHAR(15))
TRIM(CAST(date_col AS FORMAT 'mmmm-dd-yy'))
Enthusiast

Re: Convert string into date

Hi Dieter,

i am getting an error

SELECT CAST(CAST(JDATE AS FORMAT 'MMMM-DD-YY') AS VARCHAR(15))
(
CAST(JDATE AS FORMAT 'MMMM-DD-YY')) FROM USER_DBC.DUM;

getting error

expected some ting between ) TRIM Keyword?

Enthusiast

Re: Convert string into date

SELECT CAST(CAST(JDATE AS FORMAT 'MMMM-DD-YY') AS VARCHAR(15))
TRIM (
CAST(JDATE AS FORMAT 'MMMM-DD-YY')) FROM USER_DBC.DUM;

Senior Apprentice

Re: Convert string into date

Hi Rakesh,

that's two possible ways, you should use only one of them or add a comma :-)

Enthusiast

Re: Convert string into date

Hi all,
We have two databases (DEV_RETAIL and TEST_RETAIL) used for different loads in the same Teradata Box. Different people are using these environments and before promoting the stuff from RETAIL_DEV to RETAIL_TEST to Production, i would like to make sure that DDL's are exactly matching between DEV and TEST.
Using DBC.Columns table can compare DDL of 2 tables, is there any body have a query to compare table name, column name n column type of 2 different database on same Teradata box?