Need clarification in date type conversion

Database
Enthusiast

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?

3 REPLIES
Senior Apprentice

Re: Need clarification in date type conversion

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)

Enthusiast

Re: Need clarification in date type conversion

SELECT DATE '2015-06-03',DATE AS CURR_DATE,  DT FROM TMP;
Enthusiast

Re: Need clarification in date type conversion

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,