All Forums Database
dr.murthy_td 7 posts Joined 06/13
06 Jun 2013
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

D N Murthy
Kbos 20 posts Joined 04/13
06 Jun 2013

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

Bhaskar9 3 posts Joined 09/13
14 Oct 2013

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.
 

M.Saeed Khurram 544 posts Joined 09/12
14 Oct 2013

Hi,
Try this code:

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

 

Khurram

rakeshsapbo 36 posts Joined 04/13
07 Sep 2014

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?

Rakesh Reddy G

dnoeth 4628 posts Joined 11/04
07 Sep 2014

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'))

 

Dieter

rakeshsapbo 36 posts Joined 04/13
07 Sep 2014

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?

Rakesh Reddy G

rakeshsapbo 36 posts Joined 04/13
07 Sep 2014

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

Rakesh Reddy G

dnoeth 4628 posts Joined 11/04
07 Sep 2014

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

Dieter

Rkgowda82 5 posts Joined 09/14
07 Sep 2014

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?

Raja_KT 1246 posts Joined 07/09
07 Sep 2014

Using dbc, you can use for example:
select columnname as col1,columnlength as cl ....... from dbc.columns where databasename='DEV_RETAIL' and tablename='tb1'
union/minus ( your choice)
select columnname as col2,columnlength as cl2 ....... from dbc.columns where databasename='TEST_RETAIL' and tablename='tb2'
You can see from MDS too if you have.
You can see from version tool if any, if they have like SVN, pvcs, vcs .....
If you have code in unix, you can just see the difference.
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

rakeshsapbo 36 posts Joined 04/13
11 Sep 2014

Thanks Dieter.

Rakesh Reddy G

rakeshsapbo 36 posts Joined 04/13
11 Sep 2014

Hi Quick Doubt
i have applied CAST for a Date field to get the data in the below format for Date field
MMM-DD-YR

SELECT EMPLOYEE_NUMBER,MANAGER_EMPLOYEE_NUMBER,DEPARTMENT_NUMBER,JOB_CODE,LAST_NAME,FIRST_NAME,CAST(CAST(HIRE_DATE AS FORMAT 'MMM-DD-YY') AS VARCHAR(15)),

CAST(CAST(BIRTHDATE AS FORMAT 'MMM-DD-YY') AS VARCHAR(15)),SALARY_AMOUNT

----(CAST(JDATE AS FORMAT 'MMMM-DD-YY'))

 FROM CUSTOMER_SERVICE.EMPLOYEE;

 

 

 

i have used the above same query and applied TRIM 

 

SELECT EMPLOYEE_NUMBER,MANAGER_EMPLOYEE_NUMBER,DEPARTMENT_NUMBER,JOB_CODE,LAST_NAME,FIRST_NAME,TRIM(CAST(HIRE_DATE AS FORMAT 'MMM-DD-YY')) /*AS VARCHAR(15))*/,

TRIM(CAST(BIRTHDATE AS FORMAT 'MMM-DD-YY'))/* AS VARCHAR(15))*/,SALARY_AMOUNT

----(CAST(JDATE AS FORMAT 'MMMM-DD-YY'))

 FROM CUSTOMER_SERVICE.EMPLOYEE;

 

 

my ask we are getting the same result set and the same format from the above queries 

 

and also we have used VARCHAR(15) what does that signify

 

 

i know this is a elimentary doubt pls help......

Rakesh Reddy G

Langaliya.Nirav 15 posts Joined 04/11
12 Jun 2015

how to convert string like Wed Jun 10 10:43:14 2015 to Date or timestamp format?

--Nirav Langaliya

pka 1 post Joined 07/16
27 Jul 2016

Thanks Kbos.
Concatenating zero and then casting to date format worked!
 

You must sign in to leave a comment.