Mohammed Sajid

• May 16th, 2006

 

Hello,

Date is stored in the Oracle database with different bytes containg century, year, month, day, hour, minutes and seconds. Date format is only for selecting for a reporting purpose, which can be modified as required but date storage can not be modified.

Regards.

Sajid

rajanipriya

 Profile Answers by rajanipriya Questions by rajanipriya

• Jun 6th, 2006

 

thanks sajid,

one  more doubt,..can we give sysdate as value for more than one row for a column with a date data type with unique constraint

Subramani.S

• Sep 21st, 2006

 

please explain what command is used to retrieve the date Part, its urgent? 

anu_dw

 Profile Answers by anu_dw Questions by anu_dw

• Sep 25th, 2006

 

You can use this query

select to_char(sysdate,'dd') from dual;

or

select to_char(sysdate,'dd-mon-yyyy') from dual;

Regards,

Anu

Dharmendra Gohil

• Sep 26th, 2006

 

  Date Functions:
  =============
Oracle stores dates in an internal numeric format:
century,year,month,day,hours,minutes and seconds

Default display format is DD-MON-YYYY

Oracle stores the year as 1996  & 2002 and not 96 & 00

1. SYSDATE() :
    It is a function that returns:
    Date.

  select sysdate from dual;
   
2. Calculations on dates:
   date + number  = Date             ..adds a no of days to a date
   date -  number  = Date             ..subtracts a no of days from a date.
   date -  date        = No of days  ..subtracts one date from another.
   date + number/24 = Date         ..adds a number of hours to a date.

  -select dob+1 from  one;
  -select dob-1  from one;
  -select (dob1-dob) from one;
 
 - select dob1-dob from one
   no of days between dob1 & dob.

 - select  (dob1-dob)/7  from one
    no of weeks between dob1 & dob.

 - select (dob1-dob)/365 from one.
   no of years between dob1 & dob.

select first_name,hire_date,sysdate,
'since ' ||round((sysdate-hire_date)/365) ||' years'  from employees

3. Date Functions:
 
  -select months_between(dob1,dob) from one
  -select dob,add_months(dob,3) from one.
  -select next_day('20-mar-2003',1) from one OR
     ..select next_day('20-mar-2003','sun') from one
     ..returns the next sunday after the specified date.
  -select last_day(dob) from one
     ..returns the last day of the month of the specified date.
  -select dob1,round(dob1,'month') from one
     ..rounds the date to the next  month if it is > 15 
     ..rounds the date to the previous  month if it is <= 15.
e.g. select hire_date,round(hire_date,'year') from employees
select hire_date,round(hire_date,'day') from employees
     ..rounds the date to the nearest sunday.

select hire_date,round(hire_date,'month') from employees

 
e.g.
select hire_date,add_months(hire_date,3),
(hire_date+90) from employees
   

shanthi_9881

 Profile Answers by shanthi_9881 Questions by shanthi_9881

• May 8th, 2008

 

In sqlserver 2005 we can use as follows to retreive the date part of the given date or sys date
select datepart(dd,getdate())