PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 03/01/2016 3:23 PM by  Chesca
Oracle SQL reformat date
 3 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Chesca
Programmer/analyst
State
Veteran Member
(1028 points)
Veteran Member
Posts:482


Send Message:

--
03/01/2016 2:12 PM

    I am writing an SQL statement to select dates from a table and creates a file.  I want to convert date to string mm/dd/yyyy.  Any help would be greatly appreciated.

    select startdate from table; 

    this date is displayed as 31-MAY-15 so I want it displayed as 05-31-2015

    mikeP
    Private
    Private
    Veteran Member
    (407 points)
    Veteran Member
    Posts:151


    Send Message:

    --
    03/01/2016 2:23 PM
    To convert a Lawson date in GL_DATE to string:
    Here's some convert function formats

    CONVERT(CHAR, GL_DATE, 110) mm-dd-yyyy
    CONVERT(CHAR, GL_DATE, 112) yyyyddyy
    CONVERT(CHAR, GL_DATE, 101) mm/dd/yyyy

    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    03/01/2016 2:42 PM
    I'm assuming Oracle SQL due to the fact that your default date format looks like what Oracle uses.

    If so, to control the formatting of a date you would convert it to a character string using the to_char function.

    To convert to the specific format you provided: to_char(date_field,'MM-DD-YYYY')

    So to include this in a SQL statement:

    SELECT 

       TO_CHAR(empl.term_date,'MM-DD-YYYY') as "Term Date" 

     FROM prod.employee empl


    You can learn more concerning the possible date formats from here: https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm

    One quirk of Lawson to be aware of is that it doesn't use NULL values in SQL for dates. Instead any empty dates are represented by the date 01/01/1700. So you might want to roll this into a CASE statement to cover that possibility: 


    SELECT 

      CASE 

     WHEN empl.term_date = TO_DATE('01/01/1700','MM/DD/YYYY') 

       THEN '' 

     ELSE 

        TO_CHAR(empl.term_date,'MM-DD-YYYY') 

     END as "Term Date" 

     FROM prod.employee empl

    Chesca
    Programmer/analyst
    State
    Veteran Member
    (1028 points)
    Veteran Member
    Posts:482


    Send Message:

    --
    03/01/2016 3:23 PM
    Exactly what I needed, thank you all for your time and quick response!
    You are not authorized to post a reply.