Convert varchar(255) date to date

General
New Member

Convert varchar(255) date to date

Hi,

 

I know there are lots of threads out there that I've found helpful but still didn't solve my problem.  

 

I have a date 2018-09-05 (create_dt) as a date in varchar(255) (from a fastload process) format but I want to a convert to a real date format so I can do a datediff.  For example, want to do current_date - create_dt  to  get 12 days.  I've tried all the suggestions on the site:

 

SyntaxEditor Code Snippet

CAST(create_dt AS DATE FORMAT 'yyyy-mm-dd')

SyntaxEditor Code Snippet

cast(cast(create_dt  as date format 'yyyy-mm-dd') as date format 'yyyy-mm-dd') 

 but I'm still getting error 2666 which is invalid date for create_dt.

 

Is there another trick that I need to incorporate?

2 REPLIES
Teradata Employee

Re: Convert varchar(255) date to date

Hi.

 

Check the data you loaded.

 

The query is correct:

 

BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST(create_dt AS DATE FORMAT 'yyyy-mm-dd') FROM ( SELECT '2018-09-05' (VARCHAR(255)) create_dt ) a;


*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

create_dt
----------
2018-09-05

 

 

 

Cheers.

 

Carlos.

Teradata Employee

Re: Convert varchar(255) date to date

You can also use the function to_date :

select to_date(create_dt, 'yyyy-mm-dd') as create_dt
  from (select '2018-09-05' (varchar(255)) as create_dt) a;

create_dt
----------
2018-09-05