в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:51Z

1 версия

Новая страница

== Date Functions==

We must format dates to see all of the information contained in a date column.
We use the TO_CHAR function to do the converting.



<source lang="sql">
SQL> SELECT SYSDATE FROM dual;
SYSDATE
---------
29-MAY-07
SQL></source>

== Differences Between Dates==



<source lang="sql">
SQL>
SQL> CREATE TABLE dept (
2 deptID INT NOT NULL PRIMARY KEY,
3 StudentID INT NOT NULL,
4 ClassID INT NOT NULL,
5 EnrolledOn DATE,
6 Grade INT);
Table created.
SQL>
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE "2002-09-23",62);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE "2002-09-30",70);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE "2003-09-23",51);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE "2003-09-23",41);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE "2003-09-23",68);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE "2002-09-30",78);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (7,3,7,DATE "2002-09-30",80);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (8,4,8,DATE "2002-09-20",70);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (9,4,5,DATE "2002-09-20",60);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (10,5,1,DATE "2002-09-23",33);
1 row created.
SQL>
SQL>
SQL> SELECT StudentID,
2 FLOOR(MAX(CURRENT_DATE - EnrolledOn)) AS DaysEnrolled
3 FROM dept
4 GROUP BY StudentID
5 ORDER BY DaysEnrolled DESC;
STUDENTID DAYSENROLLED
---------- ------------
4 2593
1 2590
5 2590
3 2583
2 2225
5 rows selected.
SQL>
SQL>
SQL> drop table dept;
Table dropped.</source>

== LAST_DAY, TO_CHAR, TO_DATE==



<source lang="sql">
SQL>
SQL> SELECT TO_CHAR(LAST_DAY(TO_DATE("23SEP2006","ddMONyyyy")), "Month dd, yyyy") FROM dual;
TO_CHAR(LAST_DAY(T
------------------
September 30, 2006
SQL>
SQL></source>

== select date "1954-08-0 as 10,000 days==



<source lang="sql">
SQL>
SQL>
SQL> select date "1954-08-11" + 10000 as "10,000 days"
2 from dual;
10,000 da
---------
27-DEC-81
SQL></source>

== Time Interval Related Functions==

Function
Description
NUMTODSINTERVAL(x, interval_unit)
Converts the number x to an INTERVAL DAY TO SECOND with the interval for x supplied in interval_unit, which you may set to DAY, HOUR, MINUTE, or SECOND.
NUMTOYMINTERVAL(x, interval_unit)
Converts the number x to an INTERVAL YEAR TO MONTH with the interval for x supplied in interval_unit, which you may set to YEAR or MONTH.
TO_DSINTERVAL(x)
Converts the string x to an INTERVAL DAY TO SECOND.
TO_YMINTERVAL(x)
Converts the string x to an INTERVAL YEAR TO MONTH.

== Timestamp-Related Functions==

Function
Description
CURRENT_TIMESTAMP()
Returns a TIMESTAMP WITH TIME ZONE containing the current session time along with the session time zone.
EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x)
Extracts and returns a year, month, day, hour, minute, second, or time zone from x; x may be one of the timestamp types or a DATE.
FROM_TZ(x, time_zone)
Converts the TIMESTAMP x and time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE.
LOCALTIMESTAMP()
Returns a TIMESTAMP containing the current time in the session time zone.
SYSTIMESTAMP()
Returns a TIMESTAMP WITH TIME ZONE containing the current database time along with the database time zone.
SYS_EXTRACT_UTC(x)
Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC.
TO_TIMESTAMP(x, [format])
Converts the string x to a TIMESTAMP. You may also specify an optional format for x.
TO_TIMESTAMP_TZ(x, [format])
Converts the string x to a TIMESTAMP WITH TIMEZONE. You may also specify an optional format for x.

== Using Datetime Functions==

In the following table, x represents a datetime or a timestamp.
Function
Description
ADD_MONTHS(x, y)
Add y months to x. If y is negative, y months are subtracted from x.
LAST_DAY(x)
Get the last day of the month.
MONTHS_BETWEEN(x, y)
Returns the number of months between x and y. If x appears before y on the calendar, the number returned is positive.
NEXT_DAY(x, day)
Returns the datetime of the next day following x; day is specified as a literal string, for example SATURDAY.
NEW_TIME
Returns the time/day value from a time zone specified by the user.
ROUND(x [, unit])
Rounds x. By default, x is rounded to the beginning of the nearest day. You may supply an optional unit string to indicate the rounding unit.
SYSDATE()
Returns the current datetime set for the operating system.
TRUNC(x [, unit])
Truncates x. By default, x is truncated to the beginning of the day. You may supply an optional unit string that indicates the truncating unit.

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Date Timestamp Functions/Introduction - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия