In the development of Oracle databases, it is often a matter of time, so the date function of Oracle data is collected specifically. is for future investigation.
Add_months (d,n) Date d plus n months
Last_day (d) The date of the last day of the month that contains D
New_time (D,A,B) The date of the area A and?? d The date in area B and??
Next_day (D,day) than Date d?, day of the week specified by day
Sysdate the current system date and??
Greatest (D1,d2,... dn)? The last date in the list of dates
Least (D1,k2,... dn)? The earliest date in the list of dates
To_char (d [, FMT]) Date d in the format specified by FMT?? into a string
To_date (St [, FMT]) string St in the format specified by FMT?, if Fmt ignored, St to use the default format
Round (d [, FMT]) Date D is rounded to the nearest date by FMT specified format
Trunc (d [, FMT]) Date d is truncated to the most recent date by FMT specified format
To_date String type to change date type
character in the corresponding position in the string, must conform to the time range limit
Querying Oracle Date formats
----------------------------------
SELECT * from Nls_database_parameters;
The result is the following table: Nls_date_format in the table represents the date format.
PARAMETER VALUE
----------------------------------- -----------------------------------
Nls_language American
Nls_territory AMERICA
Nls_currency $
Nls_iso_currency AMERICA
Nls_numeric_characters.,
Nls_characterset ZHS16GBK
Nls_calendar Gregorian
Nls_date_format DD-MON-RR
Nls_date_language American
Nls_sort BINARY
Nls_time_format HH.MI. Ssxff AM
Nls_timestamp_format DD-MON-RR HH.MI. Ssxff AM
Nls_time_tz_format HH.MI. Ssxff AM Tzh:tzm
Nls_timestamp_tz_format DD-MON-RR HH.MI. Ssxff AM Tzh:tzm
Nls_dual_currency $
Nls_comp BINARY
Nls_nchar_characterset ZHS16GBK
Nls_rdbms_version 8.1.7.0.0
or query the V$nls_parameters table,
SELECT * from V$nls_parameters;
It's a similar result.
Sql>select to_date (' 2004-11-12 12-07-32 ', ' yyyy-mm-dd hh24-mi-ss ') value from dual;
VALUE
-------------------
2004.11.12 12:07:32
Sql>select to_date (' 20041015 ') value from dual;
VALUE
-------------------
2004.10.15 00:00:00
Sql>select to_date (' 20041315 ') value from dual;
ERROR is on line 1th:
ORA-01861: Text does not match format string
Sysdate Current date and time
Sql>select sysdate value from dual;
VALUE
-------------------
2003.11.23 17:09:01
Last_day the last day of this month
Sql>select last_day (sysdate) value from dual;
VALUE
-------------------
2003.11.30 17:08:17
Add_months (d,n) Date d after pushing n months
Sql>select add_months (sysdate,2) value from dual;
VALUE
-------------------
2005.01.23 17:10:21
Next_day (d,day) Date D in the first week, the date specified (the day of the specified week)
Sql>select next_day (sysdate,1) value from dual;
VALUE
-------------------
2004.11.28 17:38:55
[Oracle/plsql]oracle Date Processing full edition
Date Processing Full edition
To_date format
Day:
DD Number 12
DY abbreviated Fri
Day spelled out Friday
Ddspth spelled out, ordinal twelfth
Month:
MM Number 03
Mon abbreviated Mar
Month spelled out March
Year:
YY two digits 98
YYYY four digits 1998
24-hour format of the time range: 0:00:00-23:59:59 ....
12-hour format of the time range: 1:00:00-12:59:59 ....
1.
Date and character conversion function usage (TO_DATE,TO_CHAR)
2.
Select To_char (to_date (222, ' J '), ' JSP ') from dual
Show two hundred twenty-two
3.
How many days is the day of the week
Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ') from dual;
Monday
Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ', ' nls_date_language = American ') from
Dual
Monday
Set Date language
ALTER session SET nls_date_language= ' American ';
I can do that.
To_date (' 2002-08-26 ', ' yyyy-mm-dd ', ' nls_date_language = American ')
4.
Days of two days
Select Floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual;
5. The use of time as null
Select ID, active_date from table1
UNION
Select 1, to_date (null) from dual;
Note to use to_date (NULL)
6.
A_date between To_date (' 20011201 ', ' YYYYMMDD ') and to_date (' 20011231 ', ' YYYYMMDD ')
The December 31 is not included in this range until after 12 o'clock noon and December 1 of 12 points.
So, when time needs to be accurate, feel to_char is still necessary
7. Date format conflict issues
The format you enter depends on the type of Oracle character set you have installed, such as: Us7ascii, the date format type is: ' 01-jan-01 '
alter system Set Nls_date_language = American
Alter session Set Nls_date_language = American
or write in To_date.
Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ', ' nls_date_language = American ') from
Dual
Notice I'm just lifting up the nls_date_language, and of course there's plenty,
To view
SELECT * FROM Nls_session_parameters
SELECT * FROM V$nls_parameters
8.
Select COUNT (*)
From (select Rownum-1 rnum
From All_objects
where RowNum <= to_date (' 2002-02-28 ', ' yyyy-mm-dd ')-To_date (' 2002-
02-01 ', ' yyyy-mm-dd ') +1
)
where To_char (to_date (' 2002-02-01 ', ' yyyy-mm-dd ') +rnum-1, ' D ')
Not
In (' 1 ', ' 7 ')
Find days between 2002-02-28 and 2002-02-01 with the exception of Monday and seven
The dbms_utility are called before and after each other. Get_time to subtract the result (get 1/100 seconds instead of milliseconds).
9.
Select Months_between (to_date (' 01-31-1999 ', ' mm-dd-yyyy '),
To_date (' 12-31-1998 ', ' mm-dd-yyyy ')) "MONTHS" from DUAL;
1
Select Months_between (to_date (' 02-01-1999 ', ' mm-dd-yyyy '),
To_date (' 12-31-1998 ', ' mm-dd-yyyy ')) "MONTHS" from DUAL;
1.03225806451613
Usage of Next_day
Next_day (date, day)
Monday-sunday, for Format code day
Mon-sun, for format code DY
1-7, for format code D
11
Select To_char (sysdate, ' hh:mi:ss ') time from all_objects
Note: The time of the first record is the same as the last line
You can create a function to handle this problem
Create or Replace function sys_date return date is
Begin
return sysdate;
End
Select To_char (sys_date, ' Hh:mi:ss ') from all_objects;
12.
Get the number of hours
SELECT EXTRACT (HOUR from TIMESTAMP ' 2001-02-16 2:38:40 ') from offer
Sql> Select Sysdate, To_char (sysdate, ' hh ') from dual;
Sysdate to_char (sysdate, ' HH ')
-------------------- ---------------------
2003-10-13 19:35:21 07
Sql> Select Sysdate, To_char (sysdate, ' hh24 ') from dual;
Sysdate to_char (sysdate, ' HH24 ')
-------------------- -----------------------
2003-10-13 19:35:21 19
Get the date of the year and similar
13.
The processing of the month and the day
Select Older_date,
Newer_date,
Years
Months
Abs
Trunc
newer_date-
Add_months (older_date,years*12+months)
)
) days
From (select
Trunc (Months_between (newer_date, older_date)/12) YEARS,
MoD (trunc (Months_between (Newer_date, older_date)),
) MONTHS,
Newer_date,
Older_date
From (select HireDate older_date,
Add_months (hiredate,rownum) +rownum newer_date
From EMP)
)
14.
Ways to handle days of the month
Select To_char (Add_months (Last_day (sysdate) +1,-2), ' YYYYMMDD '), Last_day (sysdate) from dual
16.
Find out the number of days this year
Select Add_months (trunc (Sysdate, ' year ')-Trunc (Sysdate, "year") from dual
How to deal with leap years
To_char (Last_day (to_date ' | |: Year, ' mmyyyy '), ' DD ')
If it's 28, it's not a leap year.
17.
The difference between YYYY and RRRR
' YYYY99 To_c
------- ----
YYYY 99 0099
RRRR 99 1999
yyyy 01 0001
Rrrr 01 2001
18. Processing of different time zones
Select To_char (New_time (sysdate, ' GMT ', ' EST '), ' dd/mm/yyyy hh:mi:ss '), sysdate
from dual;
19.
5 seconds, one interval.
Select to_date (FLOOR (To_char (sysdate, ' sssss ')/300) *, ' sssss '), To_char (sysdate, ' sssss ')
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
The first day of the year
Select To_char (sysdate, ' DDD '), sysdate from dual
310 2002-11-6 10:03:51
21. Calculate hours, minutes, seconds, milliseconds
Select
Days,
A
TRUNC (a*24) Hours,
TRUNC (A*24*60-60*trunc (a*24)) Minutes,
TRUNC (A*24*60*60-60*trunc (a*24*60)) Seconds,
TRUNC (A*24*60*60*100-100*trunc (a*24*60*60)) mseconds
From
(
Select
Trunc (sysdate) days,
Sysdate-trunc (sysdate) A
From dual
)
SELECT * FROM TabName
Order by decode (mode, ' FIFO ', 1,-1) *to_char (RQ, ' Yyyymmddhh24miss ');
//
Floor ((date2-date1)/365) as the year
Floor ((date2-date1, 365)/30) as Month
MoD (mod (date2-date1, 365), 30) as day.
23.next_day function
Next_day (sysdate,6) is from the current beginning of the next Friday. The figures for the following are from Sunday onwards.
It's a simple remark.
Take Oracle's date as a special number, in days.
can be date + number = date, date-date = number, date-number = Date
The format of the Mestamp data is the same as the date data. Note that the TO_CHAR function supports date and timestamp, but Trunc does not support timestamp data types. This has made it clear that using the timestamp data type is more accurate than the date data type when the difference between two times is extremely important.
If you want to display timestamp decimal seconds information, refer to the following:
1 Select To_char (time1, ' mm/dd/yyyy HH24:MI:SS:FF3 ') "Date" from date_table
Date
-----------------------
06/20/2003 16:55:14:000
06/26/2003 11:16:36:000
In the example above, I'm only realistic about 3 bits after the decimal point.
It is easier to calculate the data differences between timestamp than the old date data type. When you subtract directly, see what happens. The results will be easier to understand, the first line of 17 days, 18 hours, 27 minutes and 43 seconds.
1 SELECT time1,
2 Time2,
3 substr ((time2-time1), InStr ((time2-time1), "+7,2") seconds,
4 substr ((time2-time1), InStr ((time2-time1), "+4,2") minutes,
5 substr ((time2-time1), InStr ((time2-time1), "+1,2") hours,
6 trunc (To_number (substr (time2-time1), 1,instr (Time2-time1, ')))
7 trunc (To_number (substr (time2-time1), 1,instr (Time2-time1, '))/7) weeks
8* from Date_table
TIME1 TIME2 SECONDS MINUTES HOURS days WEEKS
------------------------- -------------------------- ------- ------- ----- ---- -----
06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2
06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1
This means no longer needing to care about how many seconds a day is in trouble calculations. Therefore, getting the number of days, months, days, hours, minutes and seconds becomes a matter of extracting numbers with the SUBSTR function.
System date and Time
Returns the date data type in order to get the system time. You can use the Sysdate function.
Sql> SELECT sysdate from DUAL;
To get the system time, return to the timestamp data type. You can use the Systimpstamp function.
Sql> SELECT Systimestamp from DUAL;
You can set initialization parameters fixed_date Specify the Sysdate function to return a fixed value. This is used in the test date and time sensitive code. Note that this parameter is not valid for the Systimestamp function.
sql> ALTER SYSTEM SET fixed_date = ' 2003-01-01-10:00:00 ';
System altered.
Sql> select Sysdate from dual;
Sysdate
---------
01-jan-03
Sql> select Systimestamp from dual;
Systimestamp
---------------------------------------------------------
09-jul-03 11.05.02.519000 am-06:00
When using date and timestamp types, the choice is clear. You are free to dispose of date and timestamp types. When you try to switch to a more powerful timestamp, you need to be aware that they have a similar place, and there are different places, and it's enough to cause damage. Both in terms of simplicity and spacing size each have advantages, please choose reasonably.
DateDiff (month, Waterpay. Copydate, GetDate ()) = 1)
DateDiff (Day, Waterpay.copydate, GetDate ()) = 1)
The two sentences mean: to month or to return the current date (getdate) to a date field in the database (Waterpay). Copydate) is a record with a value equal to 1
How are the functions in SQL Server rewritten in Oracle?
Problem points: 20, reply times: 2
Top
1 Floor Hevin (Nothing is impossible) reply to 2005-09-25 18:12:10 score 0 The first sentence:
Months_between (To_date (To_char (sysdate, ' yyyy-mm-dd '), ' yyyy-mm-dd '),
To_date (To_char) (Waterpay. Copydate, ' yyyy-mm-dd '), ' yyyy-mm-dd '
) = 1
Top
2 Floor Hevin (Nothing is impossible) reply to 2005-09-25 18:18:06 score 20 second sentence:
To_char (sysdate-1, ' yyyy-mm-dd ') = To_char (Waterpay. Copydate, ' Yyyy-mm-dd ')
Current 1/2 page
12 Next read the full text
