All Forums Database
NewbieTD 25 posts Joined 05/13
28 Jun 2015
Need clarification in date type conversion
create volatile table tmp(Dt  date) on commit preserve rows;
insert into tmp('2015-06-22');

select dt from tmp;

dt:
2015-06-22
=============================

select date '2015-06-03',date as curr_date, date dt from tmp;

Output:
str_dt            curr_date      dt
2015-06-03  2015-06-29    2015-06-29

Hi All,
I had issue while converting column value(string/date) to date type. When i use DATE, it converts string/date(while explicitly giving the value). but when i reading value from table its taking current date(it consider DATE as alias). CAST is working fine.
Can anyone please help here?

dnoeth 4628 posts Joined 11/04
29 Jun 2015

DATE '2015-06-03' is not converting a string to a date, it's a Standard SQL's date literal :-)
To convert a string to a date you must use Standard SQL CAST(dt AS DATE) or old-style Teradata dt (DATE)

Dieter

yuvaevergreen 93 posts Joined 07/09
29 Jun 2015
SELECT DATE '2015-06-03',DATE AS CURR_DATE,  DT FROM TMP;

 

NewbieTD 25 posts Joined 05/13
29 Jun 2015
create volatile table tmp(Dt  varchar(20)) on commit preserve rows;.

insert into tmp('15/06/03');

select '2015/06/03' (date) as str_dt,date as curr_date, dt (date),dt from tmp;

Thanks a lot Dnoeth. It works fine.
Hi yuva,
I wanted to convert string to date. but in prev example gave date date type in volatile table. Thanks for your reply...
 
Thanks,
 

You must sign in to leave a comment.