PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/10/2014 8:13 PM by  thummel1
Date Format in Smart Note
 9 Replies
Sort:
You are not authorized to post a reply.
Author Messages
thummel1
Data Analyst II
Fairview Health Services
Veteran Member
(183 points)
Veteran Member
Posts:101


Send Message:

--
11/10/2014 2:38 PM
    Hi,

    Does anyone know what the SQL should be in an infoset when trying to change the format of the birthdat from mm/dd/yyyy format to just mm/dd? I am not versed enough in SQL language to change this in my Infoset. Is there a way to change the format within the step called "Define Info set'?. I saw there are options to change the date format to various things, but of course, what I wanted was not an option.

    Any tips on how to change within SQL or within the Info Set would be appreciated. Thanks.
    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    11/10/2014 3:31 PM
    Assuming you are defining the infoset with a SQL statement, you'd replace the current SQL defining your date with the following SQL - to_char(fieldname,'MM/DD')

    http://www.techonthenet.com/oracle/...o_char.php - here's a helpful page with most of the formating options avaliable to you.

    It's important to remember that at the point of conversion, SQL and everything else will consider this to be a text string, not a date.
    thummel1
    Data Analyst II
    Fairview Health Services
    Veteran Member
    (183 points)
    Veteran Member
    Posts:101


    Send Message:

    --
    11/10/2014 4:40 PM
    Thanks, I did try this as suggested, so in my Info Set it looks like this right now: and PAEMPLOYEE.BIRTHDATE = to_char(BIRTHDATE,"MM/DD"). This returns the following error message: Error: ORA-00904: "mm/dd": invalid identifier
    I tried is this way: PAEMPLOYEE.BIRTHDATE = to_char(BIRTHDATE,'MM/DD')And get this error message: Error: ORA-01843: not a valid month

    I am guessing I am close? I will also look at the link provided to see if that gives me any other clues...



    Greg Moeller
    Private
    Private
    Veteran Member
    (3873 points)
    Veteran Member
    Posts:1377


    Send Message:

    --
    11/10/2014 4:47 PM
    to_char(BIRTHDATE,'MM/DD') is definitely the correct syntax if you are using an Oracle backend. Perhaps you have invalid data in the table?
    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    11/10/2014 4:56 PM
    You must use single quotes in Oracle SQL, double quotes means something entirely different.
    thummel1
    Data Analyst II
    Fairview Health Services
    Veteran Member
    (183 points)
    Veteran Member
    Posts:101


    Send Message:

    --
    11/10/2014 4:57 PM
    I know the info set works perfectly before I add this to the info set. I've tried it in two places; one is in SQL Developer within the Query Builder, and the other is directly into the Info Set in the Smart Note. In the Info set, I am entering it under the "Having" clause. I see that SQL Developer reads this field as a date/time field. I'm not sure if that matters?
    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    11/10/2014 5:05 PM
    Also you may want to paste in a more complete portion of your code.

    If I'm mind-reading correctly, you are attempting to pull only people whose birthdate equals a set month/day combo.

    If so, that'd actually be:

    ----
    and to_char(paemployee.birthdate,'MM/DD') = '10/31'
    ----

    That would create a criteria that would select anyone with an October 31st birthday.

    If you are attempting to display the string instead, you'd be putting this into the portion of your SQL between the SELECT statement and the FROM statement.

    For instance a quick and dirty SQL to dump all employees by number and MM/DD of date of birth would be from _my system_ would be

    ----

    SELECT
    employee,
    to_char(birthdate, 'MM/DD') as "Birthday",
    /* this is where you use double quotes - to assign field names 'better' names that might include 'forbidden' characters */

    FROM
    prod.paemployee

    ---

    The output would be something along the lines of

    EMPLOYEE | Birthday
    1 | 10/31
    2 | 11/05
    3 | 01/21
    ....
    and you'll probably have to imagine the spacing looking right, still haven't gotten formatting down for this forum.
    thummel1
    Data Analyst II
    Fairview Health Services
    Veteran Member
    (183 points)
    Veteran Member
    Posts:101


    Send Message:

    --
    11/10/2014 5:11 PM
    Actually, Sam, my objective is to change the format of the Birthdate from mm/dd/yyyy hh:mm:ss to mm/dd. This is to keep the birth year confidential. Here is my "Having" Clause. I can paste more code in it if you need.
    HAVING PERSACTHST.ACTION_CODE = 'HIREACTN1'
    AND EMPLOYEE.EMP_STATUS = 'A0'
    and EMPLOYEE.COMPANY = '1000'
    and PAEMPLOYEE.BIRTHDATE = to_char(BIRTHDATE,'mm/dd')
    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    11/10/2014 5:33 PM
    You don't want to put it into the 'having' section.

    SQL is a bit different from programming languages.

    At the begining of your query there should be a SELECT section which lists all the fields are are going to be included in the output for the query.

    For instance my example above included two fields, employee and the field I chose to name Birthday.

    The next section is begun with a FROM statement. That defines (normaly) what tables are being directly hit by your query.

    In my example I am hitting only one table, prod.paemployee.

    The next section is where you define how to pick what records from the tables are being included. I don't have one in my example but they are stated with a WHERE statement.

    Next, if your query uses aggregating statements (like summing a value) then you'll have a section that explains how the query should group records together to count as one item (i.e. if you were summing an employee's pay checks you'd want to group all the checks with the same employee and company value) this is started by a GROUP BY statement.

    Next, if you need to only show certain records that have an aggregate value that matches a certain criteria, you need a section that starts with HAVING. This one works exactly like a WHERE statement except you can't do use WHERE on aggregates.

    Lastly if you need to pre-sort the output, you'd have an ORDER BY statement to start a section that lists which fields to stort by and whether it'd be an ascending or descending sort.

    ----

    So... you need to actually add the TO_CHAR(BIRTHDATE,'mm/dd') bit to your SELECT section. I would advise you do something like I did in my example and add an AS "Birthday" to it, otherwise the column name is going to be "TO_CHAR(BIRTHDAY, 'mm/dd')".

    You can either add this to the end of the list to create a new field, or if you are replacing the BIRTHDATE field entirely, swap out the line that reads

    PAEMPLOYEE.BIRTHDATE,

    with

    to_char(PAEMPLOYEE.BIRTHDATE,'mm/dd') AS "BIRTHDATE",
    thummel1
    Data Analyst II
    Fairview Health Services
    Veteran Member
    (183 points)
    Veteran Member
    Posts:101


    Send Message:

    --
    11/10/2014 8:13 PM
    Thank you, Sam. That worked perfectly!
    You are not authorized to post a reply.