Date Format in Smart Note

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.c...unctions/to_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.

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

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?

You must use single quotes in Oracle SQL, double quotes means something entirely different.

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?

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.

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')

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",

Thank you, Sam. That worked perfectly!