All Forums Database
jayakrishnab 3 posts Joined 12/13
24 Apr 2014
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))) 

 

 

david.craig 73 posts Joined 05/13
25 Apr 2014

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

Adeel Chaudhry 773 posts Joined 04/08
26 Apr 2014

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?

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.