« Previous Next »

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

Last post 10-05-2008 9:36 AM by KristoferG. 1 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (2 items)

Sort Posts:

Shortcuts

Search | Active Posts | Unanswered Posts | View all users

  • 10-05-2008, 8:50 AM

    • wlau
    • Not Ranked
    • Joined on 07-31-2008, 9:46 PM
    • Posts 8

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

    Reply Contact

    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
    Tags:

  • 10-05-2008, 9:36 AM In reply to

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

    Reply Contact

    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 ...

    Kristofer Gafvert

    http://www.gafvert.info/iis/
Page 1 of 1 (2 items)
Microsoft Communities