SQL single row function in Oracle Database --- to_date to_char

SQL single row function in Oracle Database --- to_date to_char

Date display format
First, let's take an example of the date display format ("19:35:35 ")
Year:
Yy: show two years show 13
Yyy: show three years display 013
Yyyy: Display four-digit year display 2013
Month
Mm: Display for two months 04
Mon: indicates that APR is displayed in the character set)
Month: the character set indicates that limit L is displayed)
Day
Dd number indicates the day of the current month. 05
The day of the year when the ddd number type is displayed as 095
Dy: fri for the day of the week. The Chinese version is Friday.
Day: indicates the day of the week when the character set shows Friday. The Chinese version is Friday.
Ddspth: indicates the day of the week when the character set is fully written.
Hour
Hh: two digits are displayed at 12 hours.
Hh24: two digits are displayed in 24 hours.
Minute
Mi: two numbers in 60 hexadecimal format: 35
Second
Ss: two numbers in 60 hexadecimal format: 35
Q: The quarterly display is 2.
WW: the week of the current year is displayed as 14
W: Display 1 in the week of the current month
The above is basically the date format, and there will be omissions, and then gradually improve it!
TO_DATE ()
To_date converts a string or number to a date.
TO_CHAR ()
To_char converts a date to a string in the specified format.
As for its specific usage, we will provide some examples to learn in practice.
Example:
1. to_date to_char usage [SQL] -- convert the date format to a string
Select to_char (sysdate, 'yyyy-mm-dd hh: mi: ss') AS nowTime from dual;
-- Get the year of the date. As for month and day, we will not demonstrate it.
Select to_char (SYSDATE, 'yyyy') as nowYear FROM dual;
-- Convert character query to date
Select TO_DATE ('2014/1/25 09:24:25 ', 'yyyy/mm/dd hh: mi: ss') from dual; 2. Determine the day of the week [SQL] select to_char (to_date ('2017-04-05 ', 'yyyy-mm-dd hh: mi: ss '), 'day ')
From dual;
3. Set the session language [SQL] select TO_DATE ('1970-08-26 ', 'yyyy-mm-dd', 'nls _ DATE_LANGUAGE = American ')
From dual; 4. Calculate the number of days between two dates [SQL] select floor (sysdate-to_date ('2017-08-26 ', 'yyyy-mm-dd') FROM DUAL; 5. If the time is null, use [SQL] select to_date (NULL) from dual; 6. Whether [SQL] select * from emp where hiredate between to_date ('20170101', 'yyyymmdd') in a time period ')
And to_date ('20140901', 'yyyymmdd'); 7. query the days from to except Monday and seven. [SQL] select count (*) from
(
Select rownum-1 (rnum)
From all_objects
Where rownum <= to_date ('2017-02-28 ', 'yyyy-mm-dd')-to_date ('2017-
02-01 ', 'yyyy-mm-dd') + 1
)
Where to_char (to_date ('1970-02-01 ', 'yyyy-mm-dd') + rnum-1, 'd ')
Not in ('1', '7'); 8. Calculate the number of months in a period of time [SQL] select months_between (to_date ('01-31-1999 ', 'Mm-DD-YYYY '), to_date ('12-31-1996 ',
'MM-DD-YYYY') "MONTHS" from dual; 9, year month day processing [SQL] 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), 12) MONTHS,
Newer_date,
Older_date
From (
Select hiredate older_date, add_months (hiredate, rownum) + rownum newer_date
From emp
)
;

10. method for processing the number of days in a month: [SQL] select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd '),
Last_day (sysdate) from dual; 11. Find the number of days this year [SQL] select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year ') from dual 12, computing hour, minute, millisecond
[SQL] 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)
From dual
);

OK! Here is the exercise!