pl/sql&& T-SQL 中时间处理
PL/SQL:
24小时的形式显示出来要用HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
to_date() function
1.日期格式参数 含义说明
D 一周中的星期几
DAY 天的名字，使用空格填充到9个字符
DD 月中的第几天
DDD 年中的第几天
DY 天的简写名
IW ISO标准的年中的第几周
IYYY ISO标准的四位年份
YYYY 四位年份
YYY,YY,Y 年份的最后三位，两位，一位
HH 小时，按12小时计
HH24 小时，按24小时计
MI 分
SS 秒
MM 月
Mon 月份的简写
Month 月份的全名
W 该月的第几个星期
WW 年中的第几个星期 1.日期时间间隔操作
当前时间减去7分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual
当前时间减去7小时的时间
select sysdate - interval '7' hour from dual
当前时间减去7天的时间
select sysdate - interval '7' day from dual
当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual
当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual
时间间隔乘以一个数字
select sysdate,sysdate - 8 *interval '2' hour from dual
2.日期到字符操作
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual
参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)
3. 字符到日期操作
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
具体用法和上面的to_char差不多。
4. trunk/ ROUND函数的使用
select trunc(sysdate ,'YEAR') from dual
select trunc(sysdate ) from dual
select to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual
5.oracle有毫秒级的数据类型
--返回当前时间 年月日小时分秒毫秒
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)
select to_char(current_timestamp(9),'MI:SSxFF') from dual;
6.计算程序运行的时间(ms)
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for I in 1 .. 1000
loop
open l_rc for
'select object_name from all_objects '||
'where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
to_char() function
The following are number examples for the to_char function.
|
to_char(1210.73, '9999.9') |
would return '1210.7' |
|
to_char(1210.73, '9,999.99') |
would return '1,210.73' |
|
to_char(1210.73, '$9,999.00') |
would return '$1,210.73' |
|
to_char(21, '000099') |
would return '000021' |
The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.
|
Parameter |
Explanation |
|
YEAR |
Year, spelled out |
|
YYYY |
4-digit year |
|
YYY |
Last 3, 2, or 1 digit(s) of year. |
|
IYY |
Last 3, 2, or 1 digit(s) of ISO year. |
|
IYYY |
4-digit year ｂａｓｅd on the ISO standard |
|
Q |
Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
|
MM |
Month (01-12; JAN = 01). |
|
MON |
Abbreviated name of month. |
|
MONTH |
Name of month, padded with blanks to length of 9 characters. |
|
RM |
Roman numeral month (I-XII; JAN = I). |
|
WW |
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
|
W |
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
|
IW |
Week of year (1-52 or 1-53) ｂａｓｅd on the ISO standard. |
|
D |
Day of week (1-7). |
|
DAY |
Name of day. |
|
DD |
Day of month (1-31). |
|
DDD |
Day of year (1-366). |
|
DY |
Abbreviated name of day. |
|
J |
Julian day; the number of days since January 1, 4712 BC. |
|
HH |
Hour of day (1-12). |
|
HH12 |
Hour of day (1-12). |
|
HH24 |
Hour of day (0-23). |
|
MI |
Minute (0-59). |
|
SS |
Second (0-59). |
|
SSSSS |
Seconds past midnight (0-86399). |
|
FF |
Fractional seconds. |
The following are date examples for the to_char function.
|
to_char(sysdate, 'yyyy/mm/dd'); |
would return '2003/07/09' |
|
to_char(sysdate, 'Month DD, YYYY'); |
would return 'July 09, 2003' |
|
to_char(sysdate, 'FMMonth DD, YYYY'); |
would return 'July 9, 2003' |
|
to_char(sysdate, 'MON DDth, YYYY'); |
would return 'JUL 09TH, 2003' |
|
to_char(sysdate, 'FMMON DDth, YYYY'); |
would return 'JUL 9TH, 2003' |
|
to_char(sysdate, 'FMMon ddth, YYYY'); |
would return 'Jul 9th, 2003' |
You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.
|
to_char(sysdate, 'FMMonth DD, YYYY'); |
would return 'July 9, 2003' |
|
to_char(sysdate, 'FMMON DDth, YYYY'); |
would return 'JUL 9TH, 2003' |
|
to_char(sysdate, 'FMMon ddth, YYYY'); |
would return 'Jul 9th, 2003' |
The zeros have been suppressed so that the day component shows as "9" as opposed to "09".
Oracle函数to_char转化数字型指定小数点位数的用法
to_char，函数功能，就是将数值型或者日期型转化为字符型。
比如最简单的应用：
/*1.0123--->'1.0123'*/
Select TO_CHAR(1.0123) FROM DUAL
/*123--->'123'*/
Select TO_CHAR(123) FROM DUAL
接下来再看看下面：
/*0.123 ---> '.123' */
SELEC TO_CHAR(0.123) FROM DUAL
上面的结果 '.123' 在大多数情况下都不是我们想要的结果，我们想要的应该是 '0.123'。
我们来看一下to_char函数的具体用法：
TO_CHAR ( n [, fmt [, 'nlsparam']] )
该函数将NUMBER类型的n按数值格式fmt转换成VARCHAR2类型的值。'nlsparams'指定由数值格式的元素返回的字符,包括:
．小数点字符
．组分隔符
．本地钱币符号
．国际钱币符号
变元的形式为：
'NLS_NUMERIC_CHARACTERS="dg" NLS_CURRENCY="tcxt" NLS_ISO_CURRENCY=territory'
其中d为小数点字符,g为组分隔符。
例 :TO_CHAR (17145,'L099G999','NLS_NUMERIC_CHARACTERS=".," NLS_CURRENCY="NUD"')=NUD017,145
通过上面的了解，再查看fmt的一些格式，我们可以用以下表达式得到'0.123'的值：
/*0.123 ---> ' 0.123' */
Select TO_CHAR(0.123,'0.999') FROM DUAL
/*100.12 ---> '######' */
Select TO_CHAR(100.12,'0.999') FROM DUAL
/*1.12 ---> ' 1.120' */
Select TO_CHAR(1.12,'0.999') FROM DUAL
' 0.123'是出来了，可是前面又多了一个空格。
对于 100.12 的值却是######，以及'1.12'的值变成了 '1.120'。
我们重新确定一个新的需求：
1、去空格
2、小数点最多4位，最少保留2位。
1--->'1.00'；1.1--->'1.00'；1.12-->'1.12'；1.1234--->'1.1234'；
1.12345--->'1.1235'
最终实现如下：
/*
FM ：除空格
9999999.0099：允许小数点左边最大正数为7位，小数点右边最少2位，最多4位，且在第5位进行四舍五入
*/
Select TO_CHAR(123.0233,'FM9999999.0099') FROM DUAL
PLSQL小经验
一、 Oracle的to_char()函数功能很强大但是在用它格式化数值型数据时应该请注意以下几项。如果是小数如：0.23这样的数据经过to_char后再显示会变为.23，如果你为了让它显示出原来的0.23的话就得用To_char(要格式化的数值,’0.999’)保留三个小数，并显示但这里就要注意了。他为你截取小数的时候是四舍五入了。所以如果是要求截掉小数而不四舍五入的话就应该自己写个函数截下去后再规格化。以保证它不四舍五入。
二、 To_char(1.9999,’FM90.0999’)这个函数规格化时90.0999的含义是有9的地方如果有数字就显示如果没有数字就不显示，有0的地方在没有数字的时候也会有０来占位.但这样做也有一个很大的缺点,就是如果是整数时它也仍然会显示”.”,不要小瞧这个点,一般来讲页面上要显示的话这个点就是多余的.也给我们造成了不小的麻烦.还要自己再写函数来把这个小点搞定.
三、 对于日期型的Oracle倒时提供了一个好的处理方法,可以把日期做成数值型的.然后再To_char就能显示出你所需要的类型了.
四、 在使用select into时一定要注意,这种方法你一定要确认肯定会有数据被查出时才能使用.如果查询结果为空时会导致报错.还有一种情况是查出来的数据是多条也会报错.所以应该尽量便宜游标来做.会减少错误产生的机率.
五、 还有注意一点rownum不支持排序,就是说你想用这个来控制行数的话就会发现他没有按你指定的排序方式显示.,这是一个很难办的事.而且如果你用rownum=2这样的语句来输出第二行的话也是行不通的. 六、 最恶心的一点是Oracle对null的判断变态到极点.如果你说某个变量 aa=null它是判断不出来的.尽管aa的确是空.即使在选择条件里也是判断不出来的.不知道为什么,只好用nvl()这个函数来判断了.在条件之外可以用 aa is null 来判断.
六 补充一点.就是在写存储过程时要注意参数名不能与数据库字段名相同.否则Oracle会把这个参数名看成是字段名的,即使你用表的别名区分也不行.所以起参数名的时候一定要注意这点了.
T-SQL:
Sql Server中的日期与时间函数
1. 当前系统日期、时间
select getdate()
2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值
例如：向日期加上2天
select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000
3. datediff 返回跨两个指定日期的日期和时间边界数。
select datediff(day,'2004-09-01','2004-09-18') --返回：17
4. datepart 返回代表指定日期的指定日期部分的整数。
SELECT DATEPART(month, '2004-10-15') --返回 10
5. datename 返回代表指定日期的指定日期部分的字符串
SELECT datename(weekday, '2004-10-15') --返回：星期五
6. day(), month(),year() --可以与datepart对照一下
select 当前日期=convert(varchar(10),getdate(),120)
,当前时间=convert(varchar(8),getdate(),114)
select datename(dw,'2004-10-15')
select 本年第多少周=datename(week,'2004-10-15')
,今天是周几=datename(weekday,'2004-10-15')
| 函数 | 参数/功能 |
| GetDate( ) | 返回系统目前的日期与时间 |
| DateDiff (interval,date1,date2) | 以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1 |
| DateAdd (interval,number,date) | 以interval指定的方式，加上number之后的日期 |
| DatePart (interval,date) | 返回日期date中，interval指定部分所对应的整数值 |
| DateName (interval,date) | 返回日期date中，interval指定部分所对应的字符串名称 |
参数 interval的设定值如下：
| 值 | 缩 写（Sql Server） | Access 和 ASP | 说明 |
| Year | Yy | yyyy | 年 1753 ~ 9999 |
| Quarter | q | 季 1 ~ 4 | |
| Month | Mm | m | 月1 ~ 12 |
| Day of year | Dy | y | 一年的日数,一年中的第几日 1-366 |
| Day | Dd | d | 日，1-31 |
| Weekday | Dw | w | 一周的日数，一周中的第几日 1-7 |
| Week | Wk | ww | 周，一年中的第几周 0 ~ 51 |
| Hour | Hh | h | 时0 ~ 23 |
| Minute | Mi | n | 分钟0 ~ 59 |
| Second | Ss | s | 秒 0 ~ 59 |
| Millisecond | Ms | - | 毫秒 0 ~ 999 |
access 和 asp 中用date()和now()取得系统日期时间；其中DateDiff,DateAdd,DatePart也同是能用于Access和asp中，这些函数的用法也类似
举例：1.GetDate() 用于sql server :select GetDate()
2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒
DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天
3.DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)
DatePart('d','2005-7-25 22:56:32')返回值为 25即25号
DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天
DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年
