Java and Oracle date format __oracle

Oracle and Java date formats

If you want to convert to a 2005-07-05 format, you need to use the class SimpleDateFormat

SimpleDateFormat formatt= New SimpleDateFormat ("Yyyy-mm-dd");
Date Date =new date ();
System.out.println (Formatt.format (date));

The printed format is: 2005-07-07

SimpleDateFormat formatt= New SimpleDateFormat ("Yyyy-mmmm-dd");
Date Date =new date ();
System.out.println (Formatt.format (date));

The printed format is: 2005-7 months-07

－－－－－－－－－－－－－－－－－－－－－－－－－－－

Default date format for Oracle

Sql> select Sysdate from dual;

Sysdate
----------
July-July-05

The format used to convert To_char into YYYY-MM-DD

Sql> Select To_char (sysdate, ' yyyy-mm-dd ') time from dual;
Time
----------
2005-07-07

There are two date-related functions to_date () and To_char () in Oracle.

The to_date () action converts a character type to a date type in a certain format:
Specific usage: to_date (' 2004-11-27 ', ' yyyy-mm-dd '), the former is a string, the latter is the conversion date format, note that the two should be a corresponding.
To_date (' 2004-11-27 13:34:43 ', ' yyyy-mm-dd hh24:mi:ss ') will get specific time
The to_date () action converts a character type to a date type in a certain format:
Specific usage: to_date (' 2004-11-27 ', ' yyyy-mm-dd '), the former is a string, the latter is the conversion date format, note that the two should be a corresponding.
For example, To_date (' 2004-11-27 13:34:43 ', ' yyyy-mm-dd hh24:mi:ss ') will be given specific time.

YYYY: four-bit year
Yyy,yy,y: Last three-bit, two-bit, or one-digit of year, defaults to current century
Month Number of mm:01~12
MONTH: Month of nine characters, padding on right with space
MON: Month abbreviation for three-bit characters
WW: Week of the Year
D: Day of the week
DD: Day of the month
DDD: The first day of the year
Day: The full name of the days of nine characters, padded with spaces on the right
Hh,hh12: The first few hours of the day, the 12 notation
HH24: The first few hours of the day, with a value of 00~23
MI: minutes in one hour
SS: seconds in one minute
SSSS: The number of seconds since midnight

To_char (): Convert date to character type in a certain format

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.
1 2 3 4 5 6 7
Day 123456

There are many functions about dates in Oracle

There are many functions about dates in Oracle, such as:
1, Add_months () is used to increase or decrease the number of months from a date value
Date_value:=add_months (date_value,number_of_months)
Cases:
Sql> Select Add_months (sysdate,12) "Next year" from dual;

Next year
----------
1 March-November-04

Sql> Select Add_months (sysdate,112) "Last year" from dual;

Last year
----------
1 March-March-13

Sql>

2. Current_date () returns the current date that will be placed in the time zone
Date_value:=current_date
sql> column Sessiontimezone for A15
Sql> select Sessiontimezone,current_date from dual;

Sessiontimezone Current_da
--------------- ----------
+08:00 1 March-November-03

Sql> alter session set Time_zone= ' -11:00 '
2/

The session has changed.

Sql> select Sessiontimezone,current_timestamp from dual;

Sessiontimezone Current_timestamp
--------------- ------------------------------------
-11:00 December-November-03 04.59.13.668000 Afternoon-11:
00

Sql>

3, Current_timestamp () returns the current date in the time zone by timestamp with the zone data type
Timestamp_with_time_zone_value:=current_timestamp ([timestamp_precision])
sql> column Sessiontimezone for A15
sql> Column Current_timestamp format A36
Sql> select Sessiontimezone,current_timestamp from dual;

Sessiontimezone Current_timestamp
--------------- ------------------------------------
+08:00 1 March-November-03 11.56.28.160000 a.m. + 08:
00

Sql> alter session set Time_zone= ' -11:00 '
2/

The session has changed.

Sql> select Sessiontimezone,current_timestamp from dual;

Sessiontimezone Current_timestamp
--------------- ------------------------------------
-11:00 December-November-03 04.58.00.243000 Afternoon-11:
00

Sql>

4, Dbtimezone () return time zone
Varchar_value:=dbtimezone
Sql> select Dbtimezone from dual;

DBTIME
------
-07:00

Sql>

5, extract () to find the date or interval value of the field value
Date_value:=extract (Date_field from [Datetime_value|interval_value])
Sql> Select Extract (month from sysdate) ' this month ' from dual;

This Month
----------
11

Sql> Select Extract (Year from add_months (sysdate,36)) "3 Years out" from dual;

3 Years out
-----------
2006

Sql>

6, Last_day () returns the date of the last day of the month that contains the date parameter
Date_value:=last_day (Date_value)
Sql> Select Last_day (Date ' 2000-02-01 ') "Leap Yr?" from dual;

Leap Yr?
----------
2 September-February-00

Sql> Select Last_day (sysdate) "Last day of this month" from dual;

Last Day O
----------
30月-November-03

Sql>

7, Localtimestamp () returns the date and time in the session
Timestamp_value:=localtimestamp
sql> Column Localtimestamp Format A28
Sql> select Localtimestamp from dual;

Localtimestamp
----------------------------
1 March-November-03 12.09.15.433000
Afternoon

Sql> select Localtimestamp,current_timestamp from dual;

Localtimestamp Current_timestamp
---------------------------- ------------------------------------
1 March-November-03 12.09.31.006000 1 March-November-03 12.09.31.006000 pm + 08:
Afternoon 00

Sql> alter session set time_zone= ' -11:00 ';

The session has changed.

Sql> Select Localtimestamp,to_char (sysdate, ' dd-mm-yyyy HH:MI:SS AM ') "Sysdate" from dual;

Localtimestamp sysdate
---------------------------- ------------------------
December-November-03 05.11.31.259000 13-11-2003 12:11:31 pm
Afternoon

Sql>

8, Months_between () to determine the number of months between two dates
Number_value:=months_between (Date_value,date_value)
Sql> Select Months_between (sysdate,date ' 1971-05-18 ') from dual;

Months_between (sysdate,date ' 1971-05-18 ')
----------------------------------------
389.855143

Sql> Select Months_between (sysdate,date ' 2001-01-01 ') from dual;

Months_between (sysdate,date ' 2001-01-01 ')
----------------------------------------
34.4035409

Sql>

9, Next_day () given a date value, returns the date value indicated by the second argument for the first time out now (the name string of the corresponding day should be returned)

The date letter of the Zhou phase?

1. Query the first day of the week
Select Trunc (Decode (WW, to_date) (yy | | ' 3112 ', ' yyyyddmm '), To_date (yy | | '-' || To_char (WW * 7), ' yyyy-ddd '), ' d ') last_day
From (select substr (' 2004-32 ', 1, 4) yy, To_number (substr (' 2004-32 ', 6)) WW
From dual)

Select Trunc (to_date substr (' 2003-01 ', 1,5) | | To_char (To_number (substr (' 2003-01 ', 6)) *7), ' yyyy-ddd '), ' d ')-6 first_day from dual

Select min (v_date) from
(Select (To_date (' 200201 ', ' yyyymm ') + rownum) v_date
From All_tables
where RowNum < 370)
where To_char (v_date, ' yyyy-iw ') = ' 2002-49 '

2. Query the last day of the week
Select Trunc (Decode (WW, to_date) (yy | | ' 3112 ', ' yyyyddmm '), To_date (yy | | '-' || To_char (WW * 7), ' yyyy-ddd '), ' d ')-6 first_day
From (select substr (' 2004-33 ', 1, 4) yy, To_number (substr (' 2004-33 ', 6)) WW
From dual)

Select Trunc (to_date substr (' 2003-01 ', 1,5) | | To_char (To_number (substr (' 2003-01 ', 6)) *7), ' yyyy-ddd '), ' d ') Last_day from dual

Select Max (v_date) from
(Select (To_date (' 200408 ', ' yyyymm ') + rownum) v_date
From All_tables
where RowNum < 370)
where To_char (v_date, ' yyyy-iw ') = ' 2004-33 '

3. Query the date of a week
Select Min_date, To_char (min_date, ' Day ')
(Select To_date (substr (' 2004-33 ', 1,4) | | 001 ' +rownum-1, ' yyyyddd ') min_date
From All_tables
where rownum <= decode (mod (To_number (substr (' 2004-33 ', 1,4)), 4), 0,366,365)
Union

Select To_date (substr (' 2004-33 ', 1,4) -1| |
Decode (mod (To_number (substr (' 2004-33 ', 1,4)) -1,4, 0,359,358) +rownum, ' yyyyddd ') min_date
From All_tables
where RowNum <= 7
Union

Select To_date (substr (' 2004-33 ', 1,4) +1| | ' 001 ' +rownum-1, ' yyyyddd ') min_date
From All_tables
where RowNum <= 7
)
where To_char (min_date, ' yyyy-iw ') = ' 2004-33 '