Re: How to Combine Month, Day, Year using TO_TIMESTAMP or TO_DATE function

KristoferG — Sun, 05 Oct 2008 13:36:35 GMT

Hi,

You are not giving any information whether Field4 is recognized as a TIMESTAMP or not. If it is not, you first need to convert it to a timestamp data type, and then use TO_STRING to print it out in a form you want. This would look like:

SELECT TO_STRING(TO_TIMESTAMP('Tue Sep 23 19:39:54 2008', '??? MMM dd hh:mm:ss yyyy'), 'MM/dd/yy') FROM ...

How to Combine Month, Day, Year using TO_TIMESTAMP or TO_DATE function

wlau — Sun, 05 Oct 2008 12:50:14 GMT

Hi, to all advance user...

Please help with suggestion how to combine Month, Day, Year using TO_TIMESTAMP or TO_DATE Function.

Currently the log is as follow: fi6005,INFORMATIONAL,EventLog,Tue Sep 23 19:39:54 2008,No User,The Event log service was started.

Used the following script to extract Month, Day, Year, as 3 separate fields, I would like to be MM/DD/YY as one single field.

SELECT
CASE SUBSTR(Field4,4,3)
WHEN 'Jan' THEN 1
WHEN 'Feb' THEN 2
WHEN 'Mar' THEN 3
WHEN 'Apr' THEN 4
WHEN 'May' THEN 5
WHEN 'Jun' THEN 6
WHEN 'Jul' THEN 7
WHEN 'Aug' THEN 8
WHEN 'Sep' THEN 9
WHEN 'Oct' THEN 10
WHEN 'Nov' THEN 11
WHEN 'Dec' THEN 12
END as Month,
SUBSTR(Field4,8,2) as Day,
SUBSTR(Field4,20,4) as Year

Any help is greatly appreciated, THANKS! in advance. - Winson

General Discussion

Re: How to Combine Month, Day, Year using TO_TIMESTAMP or TO_DATE function

How to Combine Month, Day, Year using TO_TIMESTAMP or TO_DATE function