Casting Varchar date value to timestamp with differnt formats

Database

Casting Varchar date value to timestamp with differnt formats

Hi,

I have varchar column which holds different values of data like 'ABC','20140424','04/24/2014','JAMES' etc.,. I have to filter only date like records from this columns so in this case it has to be '20140424','04/24/2014' and pass this value between 2 other timestamp(0) columns.

Something like column between startdate and end date ( where both start and end date are timestamp(0) columns).

I have used sys_calendar.calendar  join to filter the reocrds only which is in date format. But facing difficulty in passing that value in between timestamp, any suggestion would be a great help.

Code used for filter:

EXISTS  

(  select calendar_date from sys_calendar.calendar  where 

substring(cast(column as varchar(20)) from 1 for 10) = calendar_date (format 'mm/dd/yyyy') (CHAR(10)) )

OR EXISTS (select calendar_date from sys_calendar.calendar  where 

substring(cast(column as varchar(20)) from 1 for 10) = calendar_date (format 'mm-dd-yyyy') (CHAR(10)) ) 

 OR EXISTS (select calendar_date from sys_calendar.calendar  where 

 substring(cast(column as varchar(20)) from 1 for 10) = calendar_date (format 'yyyy-mm-dd') (CHAR(10)) )

 OR EXISTS (select calendar_date from sys_calendar.calendar  where 

 substring(cast (column as varchar(20)) from 1 for 8) = calendar_date (format 'yyyymmdd') (CHAR(8))) 

2 REPLIES
Teradata Employee

Re: Casting Varchar date value to timestamp with differnt formats

How would you interpret the character string 'JUNE'? Is this the Gregorian month name, or a first name?

Teradata Employee

Re: Casting Varchar date value to timestamp with differnt formats

Is the format of these two DATE fields fixed? Whats the format of TIMESTAMP(0) column? Are they all different columns? Can you share more clear example with input & output?