Oracle asks yesterday last month's date

To the company found that there is a report not received, after viewing the original is an Oracle data source exception.

 select  to_char (sysdate - interval " Span style= "color: #ff0000;" > '  ) as  Fromdata, To_char (sysdate -  interval  " 1   '  

Above this SQL for last month today and yesterday. In general, the error will not be.

But today is May 31, last month today is April 31. Oracle can not find, only the error.

And so on, it seems March 29, 30 also have a great chance to error.

Search for answers on the internet and use the add_months function to modify them.

SELECT   To_char (Add_months (sysdate,1'yyyyMMdd'as-'  1'day'yyyyMMdd')                    as enddatafrom      DUAL;

Re-organize time-related Oracle functions

Oracle increases and decreases time periods

Sysdate+1 Plus One day
SYSDATE+1/24 plus 1 hours
SYSDATE+1/(24*60) plus 1 minutes
SYSDATE+1/(24*60*60) plus 1 seconds
Analogy to milliseconds 0.001 seconds

Addition
Select Sysdate,add_months (sysdate,12) from dual; --plus 1 years
Select Sysdate,add_months (sysdate,1) from dual; --Add January
Select Sysdate,to_char (sysdate+7, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 weeks
Select Sysdate,to_char (sysdate+1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 days
Select Sysdate,to_char (sysdate+1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 hours
Select Sysdate,to_char (sysdate+1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 minutes
Select Sysdate,to_char (sysdate+1/24/60/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 seconds
Subtraction
Select Sysdate,add_months (sysdate,-12) from dual; -Minus 1 years
Select Sysdate,add_months (sysdate,-1) from dual; --Minus January
Select Sysdate,to_char (sysdate-7, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 weeks
Select Sysdate,to_char (sysdate-1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 days
Select Sysdate,to_char (sysdate-1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 hours
Select Sysdate,to_char (sysdate-1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 minutes
Select Sysdate,to_char (sysdate-1/24/60/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 seconds

Time function Usage:

To_date format (time: 2007-11-02 13:45:25 for example)

Year:
YY two digits two-digit year display value: 07
YYY three digits three-bit year display value: 007
YYYY four digits four-digit year display value: 2007

Month:
MM number two-bit month display value: 11
The Mon abbreviated character set represents the display value: November, if the English version shows the
The month spelled out character set represents the display value: November, if the English version, display November

Day:
DD Number the day of the month display value: 02
DDD Number the day of the year display value: 02
DY abbreviated when in days abbreviated display value: Friday, if the English version, display Fri
Day spelled out when in days full write display value: Friday, if the English version, display Friday
Ddspth spelled out, ordinal twelfth

Hour:
HH-Digits 12-hour binary display value: 01
Hh24 digits 24-hour binary display value: 13

Minute:
MI digits 60 binary display value: 45

Second:
SS-Digits 60 binary display value: 25

Other
Q Digit Quarterly display value: 4
WW Digit The first few weeks of the year display value: 44
W Digit The week of the month display value: 1

The time range in the 24-hour format is: 0:00:00-23:59:59 ....
The time range in the 12-hour format is: 1:00:00-12:59:59 ....

1. Date and character conversion function usage (TO_DATE,TO_CHAR)

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') as nowtime from dual; Date converted to string
Select To_char (sysdate, ' yyyy ') as nowyear from dual; Get the year of the time
Select To_char (sysdate, ' mm ') as nowmonth from dual; Get the month of the time
Select To_char (sysdate, ' DD ') as nowday from dual; Get the day of the time
Select To_char (sysdate, ' hh24 ') as nowhour from dual; When you get the time
Select To_char (sysdate, ' mi ') as Nowminute from dual; Get the minutes of the time
Select To_char (sysdate, ' SS ') as Nowsecond from dual; Gets the seconds of the time

Select To_date (' 2004-05-07 13:23:44 ', ' yyyy-mm-dd hh24:mi:ss ') from dual//

2.
Select To_char (to_date (222, ' J '), ' JSP ') from dual

Showing the Hundred Twenty-two

3. What 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
Setting the date language
ALTER SESSION SET nls_date_language= ' AMERICAN ';
You can do that.
To_date (' 2002-08-26 ', ' yyyy-mm-dd ', ' nls_date_language = American ')

4. Number of days between two dates
Select Floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual;

5. The use of time null
Select ID, active_date from table1
UNION
Select 1, to_date (null) from dual;

Note to use to_date (NULL)

6. Month Difference
A_date between To_date (' 20011201 ', ' YYYYMMDD ') and to_date (' 20011231 ', ' YYYYMMDD ')
It is not included in this range until after 12 o'clock noon on December 31 and before 12 o ' December 1.
So, when time needs to be accurate, I think to_char is still necessary.

7. Date format conflict issues
The input format depends on the type of Oracle character set you installed, such as: Us7ascii, the type of date format 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;
Note that I'm just lifting the nls_date_language, and of course there's a lot of
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 ')

Look for days between 2002-02-28 and 2002-02-01 except Monday and seven
Call Dbms_utility before and after each. Get_time, let's subtract the result (get 1/100 seconds instead of milliseconds).

9. Find the Month
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

One
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 the 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
Extract () Find the field value for the date or interval value
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 (
) br> sql> Select Sysdate, To_char (sysdate, ' hh24 ') from dual;

Sysdate to_char (sysdate, ' HH24 ')
-------------------------------------------
2003-10-13 19:35:21 19 /p>

13. Treatment of the month and 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 deal with the indefinite number of months
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.

The difference between 17.yyyy and rrrr
' YYYY99 To_c
------- ----
YYYY 99 0099
RRRR 99 1999
yyyy 01 0001
Rrrr 01 2001

18. Processing in different time zones
Select To_char (New_time (sysdate, ' GMT ', ' EST '), ' dd/mm/yyyy hh:mi:ss '), sysdate
from dual;

19.5 seconds Clock an 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-bit seconds

20. The day ordinal of a 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 the month
D (MoD (date2-date1, 365), 30) as day.

The 23.next_day function returns the date of the next week, day is 1-7 or Sunday-Saturday, 1 means Sunday
Next_day (sysdate,6) is from the current beginning of the next Friday. The following numbers are counted from Sunday onwards.
1 2 3 4 5 6 7
Day 123456

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

Select (Sysdate-to_date (' 2003-12-03 12:55:45 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60*60 from Ddual
Date returned is natural after conversion to SS

24,round[rounded to the nearest date] (day: rounded to the nearest Sunday)
Select Sysdate S1,
Round (sysdate) S2,
Round (sysdate, ' Year ') of year,
Round (sysdate, ' Month ') month,
Round (sysdate, ' Day ') Day from dual

25,trunc[truncated to the nearest date, in days], the date type is returned
Select Sysdate S1,
Trunc (sysdate) S2,//return current date, no time seconds
Trunc (Sysdate, ' Year ') year,//return to the current January 1, minutes and seconds
Trunc (sysdate, ' Month ') month,//return to the 1st of the current month, no time or seconds
Trunc (Sysdate, "Day") Day//Return to the current week of Sunday, no time/seconds
From dual

26, returns the latest date in the list of dates
Select Greatest (' January-January-04 ', ' April-January-04 ', ' October-February -04 ') from dual

27. Calculate the time difference
Note: The Oracle time difference is in days, so convert to month, day

Select Floor (To_number (sysdate-to_date (' 2007-11-02 15:55:03 '), ' Yyyy-mm-dd hh24:mi:ss '))/365) as Spanyears from dual//time difference-year
Select Ceil (Moths_between (Sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd Hh24:mi:ss '))) as spanmonths from dual//time difference-month
Select Floor (To_number (sysdate-to_ Date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd Hh24:mi:ss '))) as Spandays from dual//time Difference-day
Select Floor (To_number ( Sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24) as spanhours from dual//time difference-when
Select Floor (to_ Number (Sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60) as spanminutes from dual//Time difference-min
Select Floor (To_number (sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60*60) as Spanseconds from Dual//Time difference-seconds

28. Update Time
Note: Oracle time plus minus is in days, set to change the amount of N, so converted into years, days
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char ( sysdate+n*365, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//change time-year
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ' ), Add_months (Sysdate,n) as newtime from dual//change time-month
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char ( Sysdate+n, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//change Time-day
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), to _char (sysdate+n/24, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//change Time-when
Select To_char (sysdate, ' Yyyy-mm-dd hh24: Mi:ss '), To_char (SYSDATE+N/24/60, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//change time-minutes
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss '), To_char (SYSDATE+N/24/60/60, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//change time-seconds

29. Find the first day of the month and the last day
SELECT Trunc (Trunc (sysdate, ' month ')-1, ' month ') First_day_last_month,
Trunc (sysdate, ' MONTH ')-1/86400 last_day_last_month,
Trunc (sysdate, ' MONTH ') First_day_cur_month,
Last_day (Trunc (sysdate, ' MONTH ') + 1-1/86400 Last_day_cur_month
from dual;

Oracle asks yesterday last month's date