Home » SQL & PL/SQL » SQL & PL/SQL » date problem
date problem [message #429831] Thu, 05 November 2009 22:45 Go to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
hi friends
i have table xyz where there is column name as recuruitment_dt
whose data type is date, but its forat is mm/dd/yyyy
i want to change that into dd-mon-yyyy

i did it using alter session but its not working
please guide
Re: date problem [message #429832 is a reply to message #429831] Thu, 05 November 2009 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>i have table xyz where there is column name as recuruitment_dt whose data type is date, but its forat is mm/dd/yyyy

DATE datatype has NO format

TO_CHAR(recuruitment_dt, 'mm/dd/yyyy') does what you desire
Re: date problem [message #429833 is a reply to message #429831] Thu, 05 November 2009 22:57 Go to previous messageGo to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
blackswan,

TO_CHAR(recuruitment_dt, 'mm/dd/yyyy') this will convert that into character

since my table data is like this

emp no recuritment_date form_no
100 8/2/2008 1
100 8/2/2008 2
100 8/2/2008 3
100 8/2/2008 4
100 8/2/2008 5
101 2/12/2008 1
101 2/12/2008 2
101 2/12/2008 3
101 2/12/2008 4

i want to calculate maximum form_no for each emplooye for that
recuritment_date
i.e
for emp no 100 for recuritment_date 8/2/2008 :maxumum form_no
is
5

for emp no 101 for recuritment_date 2/12/2008:maxmumm form_no
is 4

in table there are around 1000 emps , they have single recuiremnet date with differener form_no

Re: date problem [message #429835 is a reply to message #429833] Thu, 05 November 2009 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

I do not understand what problem you are trying to solve.
Re: date problem [message #429839 is a reply to message #429831] Thu, 05 November 2009 23:38 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> select sysdate from dual;

SYSDATE
---------
06-NOV-09

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
06.11.2009 05:38:06

 


I am not sure this is what you are searching for.

[Updated on: Thu, 05 November 2009 23:39]

Report message to a moderator

Re: date problem [message #429841 is a reply to message #429833] Thu, 05 November 2009 23:39 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
vivek_rol wrote on Thu, 05 November 2009 22:57
blackswan,

TO_CHAR(recuruitment_dt, 'mm/dd/yyyy') this will convert that into character

since my table data is like this

emp no recuritment_date form_no
100 8/2/2008 1
100 8/2/2008 2
100 8/2/2008 3
100 8/2/2008 4
100 8/2/2008 5
101 2/12/2008 1
101 2/12/2008 2
101 2/12/2008 3
101 2/12/2008 4

i want to calculate maximum form_no for each emplooye for that
recuritment_date
i.e
for emp no 100 for recuritment_date 8/2/2008 :maxumum form_no
is
5

for emp no 101 for recuritment_date 2/12/2008:maxmumm form_no
is 4

in table there are around 1000 emps , they have single recuiremnet date with differener form_no


Calculate?

You can directly select ...

SQL> select * from vivek_rol;

     EMPNO RECURITMEN    FORM_NO
---------- ---------- ----------
       100 08/02/2008          1
       100 08/02/2008          2
       100 08/02/2008          3
       100 08/02/2008          4
       100 08/02/2008          5
       101 02/12/2008          1
       101 02/12/2008          2
       101 02/12/2008          3
       101 02/12/2008          4

9 rows selected.

SQL>  select empno,recuritment_date,max(form_no)
  2   from  vivek_rol
  3   group by  empno,recuritment_date;

     EMPNO RECURITMEN MAX(FORM_NO)
---------- ---------- ------------
       100 08/02/2008            5
       101 02/12/2008            4

SQL>


Always follow the instruction given ...
BlackSwan already told you....
Quote:
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.



Quote:
hi friends
i have table xyz where there is column name as recuruitment_dt
whose data type is date, but its forat is mm/dd/yyyy
i want to change that into dd-mon-yyyy

i did it using alter session but its not working
please guide


What about the above ?

[Updated on: Thu, 05 November 2009 23:43]

Report message to a moderator

Previous Topic: Sysdate and ToDate( )
Next Topic: NOT IN STATEMENT
Goto Forum:
  


Current Time: Tue Sep 27 09:25:18 CDT 2016

Total time taken to generate the page: 0.09206 seconds