в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:54Z

1 версия

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

== Examples of the TRUNC function being applied to datetime values==



<source lang="sql">
SQL>
SQL> DECLARE
2 date_in DATE := TO_DATE("24-Feb-2002 05:36:00 PM"
3 ,"DD-MON-YYYY HH:MI:SS AM");
4 trunc_to_year DATE;
5 trunc_to_month DATE;
6
7 BEGIN
8 trunc_to_year := TRUNC(date_in,"YYYY");
9 trunc_to_month := TRUNC(date_in,"MM");
10
11 DBMS_OUTPUT.PUT_LINE(
12 TO_CHAR(trunc_to_year, "DD-MON-YYYY HH:MI:SS AM"));
13 DBMS_OUTPUT.PUT_LINE(
14 TO_CHAR(trunc_to_month,"DD-MON-YYYY HH:MI:SS AM"));
15 END;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL></source>

== How Oracle Interprets Two-Digit Years==

The Oracle database stores all four digits of the year.
Oracle interprets the century depending on whether the YY or RR format is being used for two-digit year.
TRUNC(): truncate May 25, 2005, to the first day in the year



<source lang="sql">
SQL>
SQL>
SQL> SELECT TRUNC(TO_DATE("25-MAY-2005"), "YYYY") FROM dual;
TRUNC(TO_
---------
01-JAN-05
SQL></source>

== Truncates 7:45:26 P.M. on May to the hour==



<source lang="sql">
SQL> SELECT TO_CHAR(TRUNC(TO_DATE("25-MAY-2005 19:45:26",
2 "DD-MON-YYYY HH24:MI:SS"), "HH24"), "DD-MON-YYYY HH24:MI:SS")
3 FROM dual;
TO_CHAR(TRUNC(TO_DAT
--------------------
25-MAY-2005 19:00:00
SQL></source>

== Truncates May, to the first day in the month==



<source lang="sql">
SQL>
SQL> SELECT TRUNC(TO_DATE("25-MAY-2005"), "MM") FROM dual;
TRUNC(TO_
---------
01-MAY-05</source>

== TRUNC(date_in,"MM")==



<source lang="sql">
SQL>
SQL> DECLARE
2 date_in DATE := TO_DATE("24-Feb-2002 05:36:00 PM","DD-MON-YYYY HH:MI:SS AM");
3 trunc_to_year DATE;
4 trunc_to_month DATE;
5
6 BEGIN
7 trunc_to_month := TRUNC(date_in,"MM");
8
9 DBMS_OUTPUT.PUT_LINE(TO_CHAR(trunc_to_month,"DD-MON-YYYY HH:MI:SS AM"));
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL></source>

== TRUNC(date_in,"YYYY")==



<source lang="sql">
SQL>
SQL> DECLARE
2 date_in DATE := TO_DATE("24-Feb-2002 05:36:00 PM","DD-MON-YYYY HH:MI:SS AM");
3 trunc_to_year DATE;
4 trunc_to_month DATE;
5
6 BEGIN
7 trunc_to_year := TRUNC(date_in,"YYYY");
8
9 DBMS_OUTPUT.PUT_LINE(TO_CHAR(trunc_to_year, "DD-MON-YYYY HH:MI:SS AM"));
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL></source>

== TRUNC() parameter list==

Aspect
Parameter Description
Example
Century
CC
Two-digit century.
21
Century
SCC
Two-digit century with a negative sign (C) for B.C.
-10
Quarter
Q
One-digit quarter of the year.
1
Year
YYYY
All four digits of the year.
2006
Year
IYYY
All four digits of the ISO year.
2006
Year
RRRR
All four digits of the rounded year, which depends on the current year.
2006
Year
SYYYY
All four digits of the year with a negative sign (C) for B.C.
C1001
Year
Y,YYY
All four digits of the year with a comma.
2,006
Year
YYY
Last three digits of the year.
006
Year
IYY
Last three digits of the ISO year.
006
Year
YY
Last two digits of the year.
06
Year
IY
Last two digits of the ISO year. 06
Year
RR
Last two digits of the rounded year, which depends on the current year.
06
Year
Y
Last digit of the year.
6
Year
I
Last digit of the ISO year.
6
Year
YEAR
Name of the year in uppercase.
TWO THOUSAND-SIX
Year
Year
Name of the year with the first letter in uppercase.
Two Thousand-Six
Month
MM
Two-digit month of the year.
01
Month
MONTH
Full name of the month in uppercase, right-padded with spaces to a total length of nine characters.
JANUARY
Month
Month
Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters.
January
Month
MON
First three letters of the name of the month in uppercase.
JAN
Month
Mon
First three letters of the name of the month with the first letter in uppercase.
Jan
Month
RM
Roman numeral month.
The Roman numeral month for the fourth month (April) is IV.
Week
WW
Two-digit week of the year.
02
Week
IW
Two-digit ISO week of the year.
02
Week
W
One-digit week of the month.
2
Day
DDD
Three-digit day of the year.
103
Day
DD
Two-digit day of the month.
31
Day
D
One-digit day of the week.
5
Day
DAY
Full name of the day in uppercase.
SATURDAY
Day
Day
Full name of the day with the first letter in uppercase.
Saturday
Day
DY
First three letters of the name of the day in uppercase.
SAT
Day
Dy
First three letters of the name of the day with the first letter in uppercase.
Sat
Day
J
Julian day-the number of days that have passed since January 1, 4713 B.C.
2439892
Hour
HH24
Two-digit hour in 24-hour format.
23
Hour
HH
Two-digit hour in 12-hour format.
11
Minute
MI
Two-digit minute.
57
Second
SS
Two-digit second.
45
Second
FF[1..9]
Fractional seconds with an optional number of digits to the right of the decimal point. Only applies timestamps,When dealing with 0.123456789 seconds, FF3 would round to 0.123.
Second
SSSSS
Number of seconds past 12 a.m.
46748
Second
MS
Millisecond (millionths of a second).
100
Second
CS
Centisecond (hundredths of a second).
10
Separators
-/,.;: "text"
Characters that allow you to separate the aspects of a date and time. You can supply freeform text in quotes as a separator.
When dealing with the date December 13, 1969, DD-MM-YYYY would produce 12-13-1969 and DD/MM/YYYY would produce 12/13/1969
Suffixes
AM or PM
AM or PM as appropriate.
AM
Suffixes
A.M. or P.M.
A.M. or P.M. as appropriate.
P.M.
Suffixes
AD or BC
AD or BC as appropriate.
AD
Suffixes
A.D. or B.C.
A.D. or B.C. as appropriate.
B.C.
Suffixes
TH
Suffix to a number. You can make the suffix uppercase by specifying the numeric format in uppercase and vice versa for lowercase.When dealing with a day number of 28, ddTH would produce 28th and DDTH would produce 28TH
Suffixes
SP
Number is spelled out.When dealing with a day number of 28, DDSP would produce TWENTY-EIGHT and ddSP would produce twenty-eight
Suffixes
SPTH
Combination of TH and SP.When dealing with a day number of 28, DDSPTH would produce TWENTY-EIGHTH and ddSPTH would produce twenty-eighth
Era
EE
Full era name for Japanese Imperial, ROC Official, and Thai Buddha calendars.
No example
Era
E
Abbreviated era name.
No example
Time zones
TZH
Time zone hour.
12
Time zones
TZM
Time zone minute.
30
Time zones
TZR
Time zone region.
PST
Time zones
TZD
Time zone with daylight savings information.
No example
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813



<source lang="sql">
SQL>
SQL> select sysdate
2 , trunc(sysdate,"CC") as trunc_cc
3 from dual;
SYSDATE TRUNC_CC
--------- ---------
24-JUL-08 01-JAN-01
SQL>
SQL></source>

== TRUNC(): truncate May, to the first day in the year==



<source lang="sql">
SQL>
SQL> SELECT TRUNC(TO_DATE("25-MAY-2005"), "YYYY") FROM dual;
TRUNC(TO_
---------
01-JAN-05
SQL>
SQL>
SQL></source>

== TRUNC(x[, unit]) truncates x.==

By default, x is truncated to the beginning of the day.
If you supply an optional unit string, x is truncated to that unit.



<source lang="sql">
SQL> SELECT TRUNC(TO_DATE("25-MAY-2005"), "YYYY")
2 FROM dual;
TRUNC(TO_
---------
01-JAN-05
SQL></source>

== Use TRUNC(date_variable) when you want to extract a date from a timestamp==



<source lang="sql">
SQL>
SQL> DECLARE
2 d DATE := SYSDATE;
3 BEGIN
4 dbms_output.put_line(TO_CHAR(TRUNC(d),"DD-MON-YY HH24:MI:SS"));
5 END;
6 /
26-OCT-09 00:00:00
PL/SQL procedure successfully completed.
SQL></source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия