It appears you have not yet registered with our community. To register please click here.

Origin XT RPG Network Home

SQL Help: Date Formatting


Jul 24 2008, 04:20 AM (Post #1)
Here for the cute boys ;)
* * * * * * * * *
Posts: 16,853
Cash: 9,337,572 / 95,912
Group: Nobility
Joined: 5/08/05 04:11 AM
Here's the question:

QUOTE
Display the employees name, username, hire date, salary and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format mm/dd/yy. Salary should be rounded. Username is first two letters of the name in the lower case.


Here's the code I have so far:

CODE
SELECT SUBSTR(ename, 1, 3), empno, TO_CHAR(hiredate, 'MM/DD/YY') as Hire_Date, ROUND(sal) as SALARY, ADD_MONTHS(hiredate, 6) as REVIEW
FROM emp;


This is what I know to work. However, the further tweaking I need involves having to also change the review date format to to that of the hiredate. I have:

CODE
ADD_MONTHS(TO_CHAR(hiredate, 'MM/DD/YY', 6))


But that doesn't seem to work.

Again, you don't have to know Oracle SQL. MySQL might help too... I just need a start somewhere...
Post Options

 
Jul 24 2008, 05:21 AM (Post #2)
I Love Jingy
* * * * * * * * *
Posts: 11,212
Cash: 2,142,701,519 / 2,147,483,647
Group: Cabinet Member
Joined: 11/30/04 08:44 PM
what format are dates in already? and what format are you changing it to?
Post Options

Jul 24 2008, 05:26 AM (Post #3)
Here for the cute boys ;)
* * * * * * * * *
Posts: 16,853
Cash: 9,337,572 / 95,912
Group: Nobility
Joined: 5/08/05 04:11 AM
Dates are in DD-MM-YY.

I want to change to MM/DD/YY

I did it with the first part, but am not sure with the second.
Post Options

Jul 24 2008, 05:40 AM (Post #4)
I Love Jingy
* * * * * * * * *
Posts: 11,212
Cash: 2,142,701,519 / 2,147,483,647
Group: Cabinet Member
Joined: 11/30/04 08:44 PM
Have you tried using NLS_TIMESTAMP_FORMAT?
Post Options

Jul 24 2008, 05:47 AM (Post #5)
Here for the cute boys ;)
* * * * * * * * *
Posts: 16,853
Cash: 9,337,572 / 95,912
Group: Nobility
Joined: 5/08/05 04:11 AM
how do i use that?
Post Options

Jul 24 2008, 05:55 AM (Post #6)
I Love Jingy
* * * * * * * * *
Posts: 11,212
Cash: 2,142,701,519 / 2,147,483,647
Group: Cabinet Member
Joined: 11/30/04 08:44 PM
http://www.google.com/search?hl=en&q=oracl...G=Google+Search

I don't use oracle. you could always do string parsing, but i'm sure the build in support is better.
Post Options