怎么随机去一个月中的某个时段？

Question

怎么随机去一个月中的某个时段？
比如 2008-7
随机取出来的就像 2008-7-7 13:44:24 ——13:55:46

Answer 1

表
create table t_charge  (
   cid                NUMBER                          not null,
   cstart             VARCHAR2(60),
   cend               VARCHAR2(60),
   ctotal             NUMBER,
   cnumber            VARCHAR2(60),
   constraint PK_T_CHARGE primary key (cid)
);comment on table t_charge is
'话费表';comment on column t_charge.cid is
'话费编号';comment on column t_charge.cstart is
'通话开始时间';comment on column t_charge.cend is
'通话结束时间';comment on column t_charge.ctotal is
'话费统计';comment on column t_charge.cnumber is
'所属号码';

Answer 2

rand()*30得出一个随机日，类似得到时分秒，拼接字符串，转化格式to_date，就是不知道是不是你想要的结果，呵呵

Answer 3

select round(dbms_random.value(a.minday,a.maxday))||' '||
        round(dbms_random.value(a.minhour,a.maxhour))||':'||
        round(dbms_random.value(a.minm,a.maxm))||':'||
        round(dbms_random.value(a.mins,a.maxs))
from(  
  select to_char(last_day(to_date('200807','yyyy/mm')),'dd') maxday,
           '01' minday,
           '01' minhour,
           '12' maxhour,
           '01' minm,
           '59' maxm,
           '01' mins,
           '59' maxs
    from dual) a

Answer 4

这段SQL可以让你随机生成某一天某个时间段，输入时只要输入2008－7或者2008-9之类的就行了SELECT a.days + LEAST (b.t_1, b.t_2),
         a.days
       + DECODE (b.t_1,
                 b.t_2, LEAST (b.t_1, b.t_2) + 0.00001,
                 GREATEST (b.t_1, b.t_2)
                )
  FROM (SELECT   TO_DATE (:s_day, 'yyyy-mm')
               + ROUND
                    (DBMS_RANDOM.VALUE
                            (1,
                             TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE (:s_day,
                                                                    'yyyy-mm'
                                                                   )
                                                          ),
                                                 'dd'
                                                )
                                       )
                            )
                    ) days
          FROM DUAL) a,
       (SELECT ROUND (DBMS_RANDOM.VALUE (0, 1), 5) t_1,
               ROUND (DBMS_RANDOM.VALUE (0, 1), 5) t_2
          FROM DUAL) b

Answer 5

如果是同一个月的就更简单了
SELECT TO_DATE (:s_day, 'yyyy-mm') + LEAST (b.t_1, b.t_2),
         TO_DATE (:s_day, 'yyyy-mm')
       + DECODE (b.t_1,
                 b.t_2, LEAST (b.t_1, b.t_2) + 0.00001,
                 GREATEST (b.t_1, b.t_2)
                )
  FROM (SELECT ROUND
                  (DBMS_RANDOM.VALUE
                            (0,
                             TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE (:s_day,
                                                                    'yyyy-mm'
                                                                   )
                                                          ),
                                                 'dd'
                                                )
                                       )
                            ),
                   5
                  ) t_1,
               ROUND
                  (DBMS_RANDOM.VALUE
                            (0,
                             TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE (:s_day,
                                                                    'yyyy-mm'
                                                                   )
                                                          ),
                                                 'dd'
                                                )
                                       )
                            ),
                   5
                  ) t_2
          FROM DUAL) b