SQL中取一个月的数据的处理方式

前提：

场景设置：表T，其中有属性date表示表中数据的更新时间。

问题：取一个月的数据：2015 年1月的数据。

    取前一个月的数，当前月份未知。

一，取一个月的数据

对于知道确定月份的需求，SQL处理很简单：

SELECT * FROM T WHERE TO_CHAR(date,'yyyy-mm')='2015-01';

二，取前一个月的数

这个就需要从sysdate着手，需要从此获取到前一个月的具体月份值：

参考：http://www.cnblogs.com/mybi/archive/2012/09/05/2671761.html

SELECT CONCAT(TO_CHAR(SYSDATE, 'yyyy') ||
              TO_CHAR(ADD_MONTHS(SYSDATE, -1), '-MM-'), '01 00:00:00') START_TIME
      ,
       
       CONCAT(TO_CHAR(SYSDATE, 'yyyy') ||
              TO_CHAR(ADD_MONTHS(SYSDATE, -1), '-MM-') ||
              TO_CHAR(LAST_DAY(SYSDATE), 'dd '), '23:59:59') END_TIME
  FROM DUAL;

  结果：

解读：

1,CONCAT(字串1, 字串2, 字串3, ...):

将字串1、字串2、字串3，等字串连在一起。请注意，Oracle的CONCAT()只允许两个参数；换言之，一次只能将两个字串串连起来。不过，在Oracle中，我们可以用'||'来一次串连多个字串。

• MySQL: CONCAT()
• Oracle: CONCAT(), ||
• SQL Server: + 

2,T0_CHAR(,):

             参考：http://www.cnblogs.com/reborter/archive/2008/11/28/1343195.html

表 5-7. 格式化函数

 

函数	返回	描述	例子
to_char(timestamp, text)	text	把 timestamp 转换成 string	to_char(timestamp 'now','HH12:MI:SS')
to_char(int, text)	text	把 int4/int8 转换成 string	to_char(125, '999')
to_char(float, text)	text	把 float4/float8 转换成 string	to_char(125.8, '999D9')
to_char(numeric, text)	text	把 numeric 转换成 string	to_char(numeric '-125.8', '999D99S')
to_date(text, text)	date	把 string 转换成 date	to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)	date	把 string 转换成 timestamp	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)	numeric	把 string 转换成 numeric	to_number('12,454.8-', '99G999D9S')

表 5-8. 用于 date/time 转换的模板
 

模板	描述
HH	一天的小时数 (01-12)
HH12	一天的小时数 (01-12)
HH24	一天的小时数 (00-23)
MI	分钟 (00-59)
SS	秒 (00-59)
SSSS	午夜后的秒 (0-86399)
AM or A.M. or PM or P.M.	正午标识（大写）
am or a.m. or pm or p.m.	正午标识（小写）
Y,YYY	带逗号的年（4 和更多位）
YYYY	年（4和更多位）
YYY	年的后三位
YY	年的后两位
Y	年的最后一位
BC or B.C. or AD or A.D.	年标识（大写）
bc or b.c. or ad or a.d.	年标识（小写）
MONTH	全长大写月份名（9字符）
Month	全长混合大小写月份名（9字符）
month	全长小写月份名（9字符）
MON	大写缩写月份名（3字符）
Mon	缩写混合大小写月份名（3字符）
mon	小写缩写月份名（3字符）
MM	月份 (01-12)
DAY	全长大写日期名（9字符）
Day	全长混合大小写日期名（9字符）
day	全长小写日期名（9字符）
DY	缩写大写日期名（3字符）
Dy	缩写混合大小写日期名（3字符）
dy	缩写小写日期名（3字符）
DDD	一年里的日子(001-366)
DD	一个月里的日子(01-31)
D	一周里的日子(1-7；SUN=1)
W	一个月里的周数
WW	一年里的周数
CC	世纪（2 位）
J	Julian 日期（自公元前4712年1月1日来的日期）
Q	季度
RM	罗马数字的月份（I-XII；I=JAN）－大写
rm	罗马数字的月份（I-XII；I=JAN）－小写

所有模板都都允许使用前缀和后缀修改器。模板里总是允许使用修改器。前缀　'FX' 只是一个全局修改器。

表 5-9. 用于日期/时间模板 to_char() 的后缀

 

后缀	描述	例子
FM	填充模式前缀	FMMonth
TH	大写顺序数后缀	DDTH
th	小写顺序数后缀	DDTH
FX	固定模式全局选项（见下面）	FX Month DD Day
SP	拼写模式（还未实现）	DDSP

用法须知：

• • 如果没有使用 FX 选项，to_timestamp 和 to_date 忽略空白。FX 必须做为模板里的第一个条目声明。
  • 反斜杠（"\"）必须用做双反斜杠（"\\"），例如'\\HH\\MI\\SS'。
  • 双引号（'"'）之间的字串被忽略并且不被分析。如果你想向输出写双引号，你必须在双引号前面放置一个双反斜杠（'\\'），例如'\\"YYYY Month\\"'。
  • to_char 支持不带前导双引号（'"'）的文本，但是在双引号之间的任何字串会被迅速处理并且还保证不会被当作模板关键字解释（例如：'"Hello Year: "YYYY'）。

                 参考：《 Oracle 中的 TO_DATE 和 TO_CHAR 函数 日期处理》http://www.cnblogs.com/jiangchongwei/archive/2011/05/02/2034273.html

此参考文章，对时间的处理，列举的非常详尽，但是缺少说明和解析。

参考：《TO_CHAR_转换函数的用法 》http://blog.youkuaiyun.com/huangbiao86/article/details/6607939

此参考文章中，列举了FORMAT中所有符号的解析，在FOMCAT中可以自行组合：

TO_CHAR ( date [, fmt [, 'nlsparam'] ])

它的作用是将一个日期date转换成一个指定格式fmt的字符串。有关nlsparam基本可以不管，它是用来确定返回的日期的名字以及缩写词等的形式，如你的数据库语言如果是英语，那么返回的月份表示可能是类似这样的“MAY”；而如果你的数据库语言是汉语，那么返回的月份表示可能是类似这样的“五月”。nlsparam就是用来改变这些显示，如假设你的数据库语言是汉语，而你像把它变成英语的表示，则可以这样设置：nls_date_language=’english’。99％可以不用管这个参数。如下面这个例子：

10:13:29 SQL> select to_char(sysdate,'yyyy-mm-dd') today from dual;

 可能大家问题最多的就是fmt这个格式设置有这些：

元素	表示含义
- / , . ; : “test”	这些标点符号和具体的字符串，最终会显示出来，其实他们启到的作用就是分隔，如下面这个日期： 2007-9-16 这里符合 “-”就启到了分隔年月日的作用。
AD A.D.	公元后
BC B.C.	公元前
AM A.M.	早上，上午
PM P.M.	下午
D	一周的第几天，星期天是1
DAY	星期几，，一共用9个字符大小来显示，如果没有这么长，名称的右边用空格来填充
DD	一个月的第几天，【1～31】
DDD	一年的第几天，【1～366】
DY	星期几，但是是简称，如周一可能就是MON
FF[0..9]	这个是用于timestamp类型的，用于表示将秒分成多少位数来表示，在FF后面跟上一个0到9的数字。
HH	小时，【1～12】
HH12	小时，【1～12】
HH24	小时，【0～23】
MI	分钟，【0～59】
MM	月份，【01~12】
MON	月份，但是是月份的简称，如1月可能是JAN来表示
MONTH	月份，月份的名称，如9月可能是september来表示，一共用9个字符大小来显示，如果没有这么长，名称的右边用空格来填充
Q	季节，【1，2，3，4】分别代表春夏秋冬
SS	秒，【0～59】
SSSS	从午夜0秒开始计算到现在的秒数
WW	一年的第几个周，【1～53】
W	一个月的第几个周，【1～5】
Y,YYY	年份，中间有个逗号
YEAR SYEAR	年份，用字符串显示，S表示指示公元前（BC）使用“-”表示
YYYY SYYY	4个数字的年份，S表示指示公元前（BC）使用“-”表示
YYY YY Y	分别表示年份的后面三个、两个、一个数字

3，add_months():

格式：ADD_MONTHS(DATE,NUMBER)即ADD_MONTHS(日期，数字）

ADD_MONTHS函数在输入日期上加上指定的几个月返回一个新的日期。如果给出一负数，返回值日期之前几个月日期。

 ADD_MONTHS(DATE,NUMBER)中的NUMBER应当是整数，给出小数时，正数被截为小于该数的最大整数，负数被截为大于该数的最小整数。

例：add_months(to_date('29-Feb-96','d-mon-yyyy'),-12.99)  返回  28－Feb-95

注：上例中29调整为28，是因为96年二月份最后一天是29号，而95年二月份最后一天是28号。

add_months(to_date('15-Nov-1961','d-mon-yyyy'),1) 返回   15-Dec-1961

add_months(to_date('30-Nov-1961','d-mon-yyyy'),1) 返回   31-Dec-1961

注：从30调整为31，为了保持都是对应最后一天。

add_months(to_date('31-Jan-1999','d-mon-yyyy'),1) 返回   28-Feb-1999

注：函数将31日调为28日，以使结果对应新一月的最后一天，因1999年2月只有28天。

4，last_day(d):

  --时间点d当月份最后一天  
  
  ex.  
   select sysdate, LAST_DAY(sysdate) LAST_DAY from dual;  
  
   SYSDATE    LAST_DAY  
   ---------- ---------  
   21-SEP-07  30-SEP-07   

参考：《oracle日期函数last_day得到当每月第一天与最后一天》http://www.360sdn.com/oracle/2013/0615/401.html

此文章中对last_day函数有多种运用方法。