Oracle Dates and Times

转载 2006年06月06日 09:13:00

Oracle Dates and Times 


Oracle supports both date and time, albeit differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also the hour, minute, and second.

The DATE type is used in the same way as other built-in types such as INT. For example, the following SQL statement creates a relation with an attribute of type DATE:

create table x(a int, b date);
DATE Format

When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle's default format for DATE is "DD-MON-YY". Therefore, when you issue the query
select b from x;you will see something like:
01-APR-98Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format. For example,


returns the result:


The general usage of TO_CHAR is:
TO_CHAR(<date>, '<format>')
where the <format> string can be formed from over 40 options. Some of the more popular ones include:

MM Numeric month (e.g., 07)
MON Abbreviated month name (e.g., JUL)
MONTH Full month name (e.g., JULY)
DD Day of month (e.g., 24)
DY Abbreviated name of day (e.g., FRI)
YYYY 4-digit year (e.g., 1998)
YY Last 2 digits of the year (e.g., 98)

Like YY, but the two digits are ``rounded'' to a year
in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906

AM (or PM) Meridian indicator
HH Hour of day (1-12)
HH24 Hour of day (0-23)
MI Minute (0-59)
SS Second (0-59)
You have just learned how to output a DATE value using TO_CHAR. Now what about inputting a DATE value? This is done through a function called TO_DATE, which converts a string to a DATE value, again according to the DATE format. Normally, you do not have to call TO_DATE explicitly: Whenever Oracle expects a DATE value, it will automatically convert your input string using TO_DATE according to the default DATE format "DD-MON-YY". For example, to insert a tuple with a DATE attribute, you can simply type:

insert into x values(99, '31-may-98');Alternatively, you may use TO_DATE explicitly:
insert into x
values(99, to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));The general usage of TO_DATE is:
TO_DATE(<string>, '<format>')where the <format> string has the same options as in TO_CHAR.
Finally, you can change the default DATE format of Oracle from "DD-MON-YY" to something you like by issuing the following command in sqlplus:

alter session set NLS_DATE_FORMAT='<my_format>';The change is only valid for the current sqlplus session.

The Current Time

The built-in function SYSDATE returns a DATE value containing the current date and time on your system. For example,

select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time" from dual;

Current Time
Tue 21-Apr-1998 21:18:27

which is the time when I was preparing this document :-) Two interesting things to note here:

You can use double quotes to make names case sensitive (by default, SQL is case insensitive), or to force spaces into names. Oracle will treat everything inside the double quotes literally as a single name. In this example, if "Current Time" is not quoted, it would have been interpreted as two case insensitive names CURRENT and TIME, which would actually cause a syntax error.

DUAL is built-in relation in Oracle which serves as a dummy relation to put in the FROM clause when nothing else is appropriate. For example, try "select 1+2 from dual;".
Another name for the built-in function SYSDATE is CURRENT_DATE. Be aware of these special names to avoid name conflicts.

Operations on DATE

You can compare DATE values using the standard comparison operators such as =, !=, >, etc.
You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. In general, the result may contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two DATE values are not allowed.

You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. For example, SYSDATE+1 will be tomorrow. You cannot multiply or divide DATE values.

With the help of TO_CHAR, string operations can be used on DATE values as well. For example, to_char(<date>, 'DD-MON-YY') like '%JUN%' evaluates to true if <date> is in June.

flask入门的教程-日期和时间 Dates and Times

文章转自 : 感谢原作者的付出 转载时间为:2014-05-06
  • oXiFangShiBai
  • oXiFangShiBai
  • 2014年05月06日 01:32
  • 883


GC 日志是一个分析Java内存回收有用的工具,它能准确的记录每一次GC的执行时间和结果 GC or Full GC关于Full GC,容易有误解,以为是堆全部年代内存GC;其实”Full” 并不是用...
  • wenhuayuzhihui
  • wenhuayuzhihui
  • 2016年08月24日 14:19
  • 926


数学符号的英文表达 一、小数、分数、百分数 1. 小数表示法 小数点左边的数通常按基数词读,若为三位以上的数,也可按编码式读法读出,即将数字单个读出;小数点右边的数通常按编码式读法单个读出。如:...
  • zhaobryant
  • zhaobryant
  • 2014年07月22日 20:38
  • 1146


HADOOP2.7.0为测试版本,2.7.1才是正式版 由于网络上向下载的hadoop-2.7.1.tar.gz无法在64位系统运行【存在native 32->64问题等】,所以下载源代码自行编译了,...
  • lbyyy
  • lbyyy
  • 2015年10月09日 16:33
  • 2028

Android studio so库找不到问题

java.lang.UnsatisfiedLinkError: dalvik.system.PathClassLoader[DexPathList[[ zip file "/dat...
  • u013164293
  • u013164293
  • 2017年04月25日 10:38
  • 1141

android 项目中so 文件的问题

1.指定加载指令集包下的so文件 引用: React Native项目...
  • sinat_32955803
  • sinat_32955803
  • 2017年04月05日 13:03
  • 1974

python numpy学习笔记(5)

  • wxh928408225
  • wxh928408225
  • 2017年07月26日 01:26
  • 199

Objective-C代码规范 Objective-C代码规范中文简易注释 The official Objective-C style guide. ...
  • qinwei008
  • qinwei008
  • 2015年01月09日 19:50
  • 429

Cloud Times,我们用Go语言

本文章选自于尚观云科技 云时代,我们面对着是一个大量而且复杂的时代,这里边数据的体量很大、也很多,所有我们可能在高并发的时候,我们的代码也许会出现种种问题,但是,Go语言为我们解决了这个问题,这个语...
  • shangguanyun2017
  • shangguanyun2017
  • 2017年12月25日 14:23
  • 150

linux times函数分析。

时钟滴答(clock tick) 请问时间的嘀嗒数是根据什么来设定的 有必要明确一些Linux内核时钟驱动中的基本概念。 (1)时钟周期(clock cycle)的频率:8253/8254 PI...
  • tankaro
  • tankaro
  • 2013年05月11日 17:57
  • 2050
您举报文章:Oracle Dates and Times