DATE FUNCTION IN SQL

SQL>create table t (datecol1 date,datecol2 date,datecol3 date);

 Table created

SQL>insert into t values(sysdate+23,sysdate-10,sysdate-24);

SQL>select * from t ;

DATECOL1   DATECOL2   DATECOL3

----------------   ----------------  -----------------

09-mar-13         04-feb-13      21-jan-13

01-jan-12           04-mar-12    23-dec-13

14-feb-11           31-jan-11     16-aug-11

SQL>select max(date  col1) from t;

MAX(date col1)

-------------------

14-feb-11

SQL>select min(date  col3) from t;

MIN(date col13)

-------------------

21-jan-13

SQL>create table order (oid int,pname varchar(10),odate date datetime NOTNULL DEFAULT GETDATE( ));

 Table created

SQL>select GETDATE() from order;

2008-11-11 12:45:34.243

SQL>select DATE PART(yyyy,odate)as order year,DATEPART(mm,odate)as order month from order where oid=1;

ORDER YEAR  ORDER MONTH

--------------------  ----------------------

2008                       11

SQL>select oid,DATEADD(day,45,orderdate)as order pay date from orders;

OID   ORDER PAY DATE

------   --------------------------

1                    2008-12-26  13:23:44.647

SQL>DATEDIFF(day,’2008-06-05’,’2008-0806’) as diff date from orders;

DIFFDATE

---------------

61

SQL>convert(varchar(19),GETDATE())convert(varchar(10) GETDATE(),110) convert(varchar(11) GETDATE(),6));

Nov 04 2011                      11:45 pm

11-04-2011

04  Nov  11