因为round不能处理字符型数据,所以报错,我们要将字符型数据转换为日期型 idlegt; select round(to_date(
日期函数的处理
如果你对当前系统的日期格式 看这不舒服 可以修改当前会话的显示格式
idle> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
Session altered.
idle> select sysdate from dual;
SYSDATE
-------------------
2010-12-17 08:52:31
idle> 这是OS系统时间 数据库本身没时间 只有SCN号
日期可以直接参与运算
idle> select sysdate-10 from dual;
SYSDATE-10
-------------------
2010-12-07 08:53:16
两个日期型数据相减会得到相差的天数
idle> select to_date('2010-12-30')-sysdate from dual;
TO_DATE('2010-12-30')-SYSDATE
-----------------------------
12.6290856
idle>
可以将日期型的数据和一个小时数相加减 这个数要除以24
idle> select sysdate from dual;
SYSDATE
-------------------
2010-12-17 08:55:56
idle> select sysdate + 5/24 from dual;
SYSDATE+5/24
-------------------
2010-12-17 13:55:56
idle>
计算scott的工龄
idle> select ename,(sysdate-hiredate)/365 "years" from emp where ename='SCOTT';
ENAME years
---------- ----------
SCOTT 23.6804732
idle>
日期型函数
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND 和 TRUNC 对日期的取舍
MONTHS_BEWTEEN(日期1,日期2)
如果日期1大于日期2返回正数,日期1小于日期2返回负数
idle> select months_between('2010-10-10','2010-12-10') from dual;
MONTHS_BETWEEN('2010-10-10','2010-12-10')
-----------------------------------------
-2
idle> select months_between('2010-12-10','2010-10-10') from dual;
MONTHS_BETWEEN('2010-12-10','2010-10-10')
-----------------------------------------
2
idle>
ADD_MONTHS(日期,n)
把n个月加到日期上
idle> select add_months('2010-10-10',3) from dual;
ADD_MONTHS('2010-10
-------------------
2011-01-10 00:00:00
idle>
NEXT_DAY(日期,星期)
从当天算起,求下一个指定星期几是几号. 如果是中文系统将MONDAY改成"星期一"
idle> select next_day(sysdate,'MONDAY') from dual;
NEXT_DAY(SYSDATE,'M
-------------------
2010-12-20 09:02:06
idle>
idle> select next_day(sysdate,'fri') from dual;
NEXT_DAY(SYSDATE,'F
-------------------
2010-12-24 09:02:44
idle>
LAST_DAY(日期)
返回该日期的所在月的最后一天
idle> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-------------------
2010-12-31 09:03:26
idle>
idle> alter session set nls_date_format='YYYY-MM-DD';
Session altered.
idle> select ename,hiredate,last_day(hiredate),next_day(hiredate,'SUN'),months_between(sysdate,hiredate) "MON",ADD_MONTHS(hiredate,3) from emp
where ename='SCOTT';
ENAME HIREDATE LAST_DAY(H NEXT_DAY(H MON ADD_MONTHS
---------- ---------- ---------- ---------- ---------- ----------
SCOTT 1987-04-19 1987-04-30 1987-04-26 283.947709 1987-07-19
idle>
ROUND(date,'[day|month|year]') 和 trunc(date,'[day|month|year]') 应用于日期型数据
数字的进位和截取是以小数点为中心,而日期的进位和截取是以年月日时分秒为中心
idle> select round('2010-10-10','MONTH') from dual;
select round('2010-10-10','MONTH') from dual
*
ERROR at line 1:
ORA-01722: invalid number

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.

The article discusses creating indexes on JSON columns in various databases like PostgreSQL, MySQL, and MongoDB to enhance query performance. It explains the syntax and benefits of indexing specific JSON paths, and lists supported database systems.

Article discusses using foreign keys to represent relationships in databases, focusing on best practices, data integrity, and common pitfalls to avoid.

Article discusses securing MySQL against SQL injection and brute-force attacks using prepared statements, input validation, and strong password policies.(159 characters)


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Dreamweaver Mac version
Visual web development tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

SublimeText3 Chinese version
Chinese version, very easy to use

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
