oracle mysql date_Mysql date_format 与 Oracle to_char(date,’format’)

Mysql

date_format

与

Oracle to_char(date,’format’)

以上

2

种方法均属于把事件类型的列，转换成你所需要的时间格式的函数。

Oracle:

select

to_char(zatime,

'yyyymmdd hh24:mi:ss'

)

from

downloaddata

where

to_char(zatime,

'yyyymmdd hh24:mi:ss'

)>

'20120405 23:15:18'

and

rownum

<

5;

TO_CHAR(ZATIME,'YYYYMMDDHH24:M

------------------------------

20120406 12:15:30

20120406 12:16:04

20120406 12:16:08

20120406 12:17:17

Mysql:

select date_format(CreateDate,'%Y%m%d

%H:%i:%s') from tbl_RelatedArticle where date_format(CreateDate,'%Y%m%d

%H:%i:%s') >= '20120506 11:58:12' limit 4;

date_format(CreateDate,'%Y%m%d

%H:%i:%s')

'20120507

10:51:47'

'20120507

10:52:02'

'20120507

15:20:00'

'20120508

10:13:32'

上面的

2

个例子应该基本上可以了解

date_format

的功能，其实还有一些功能这里暂时不介绍了。下面介绍一下官档中的描述。

(总结：今天也了解了一下

MYSQL

查询中以时间为标准分割查询。)

官档链接

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Formats the

date

value according to the

format

string.

The following specifiers may be used in the

format

string. The “

%

” character is required before format specifier characters.

Specifier

Description

%a

Abbreviated weekday name (

Sun

..

Sat

)

%b

Abbreviated month name (

Jan

..

Dec

)

%c

Month, numeric (

0

..

12

)

%D

Day of the month with English suffix (

0th

,

1st

,

2nd

,

3rd

, …)

%d

Day of the month, numeric (

00

..

31

)

%e

Day of the month, numeric (

0

..

31

)

%f

Microseconds (

000000

..

999999

)

%H

Hour (

00

..

23

)

%h

Hour (

01

..

12

)

%I

Hour (

01

..

12

)

%i

Minutes, numeric (

00

..

59

)

%j

Day of year (

001

..

366

)

%k

Hour (

0

..

23

)

%l

Hour (

1

..

12

)

%M

Month name (

January

..

December

)

%m

Month, numeric (

00

..

12

)

%p

AM

or

PM

%r

Time, 12-hour (

hh:mm:ss

followed by

AM

or

PM

)

%S

Seconds (

00

..

59

)

%s

Seconds (

00

..

59

)

%T

Time, 24-hour (

hh:mm:ss

)

%U

Week (

00

..

53

), where Sunday is the first day of the week

%u

Week (

00

..

53

), where Monday is the first day of the week

%V

Week (

01

..

53

), where Sunday is the first day of the week; used with

%X

%v

Week (

01

..

53

), where Monday is the first day of the week; used with

%x

%W

Weekday name (

Sunday

..

Saturday

)

%w

Day of the week (

0

=Sunday..

6

=Saturday)

%X

Year for the week where Sunday is the first day of the week, numeric, four digits; used with

%V

%x

Year for the week, where Monday is the first day of the week, numeric, four digits; used with

%v

%Y

Year, numeric, four digits

%y

Year, numeric (two digits)

%%

A literal “

%

” character

%

x

x

, for any “

x

” not listed above

Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as

'2014-00-00'

.

The language used for day and month names and abbreviations is controlled by the value of the

lc_time_names

system variable (

Section 10.7, “MySQL Server Locale Support”

).

DATE_FORMAT()

returns a string with a character set and collation given by

character_set_connection

and

collation_connection

so that it can return month and weekday names containing non-ASCII characters.

mysql>

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

-> 'Sunday October 2009'

mysql>

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');

-> '22:23:00'

mysql>

SELECT DATE_FORMAT('1900-10-04 22:23:00',

->

'%D %y %a %d %m %b %j');

-> '4th 00 Thu 04 10 Oct 277'

mysql>

SELECT DATE_FORMAT('1997-10-04 22:23:00',

->

'%H %k %I %r %T %S %w');

-> '22 22 10 10:23:00 PM 22:23:00 00 6'

mysql>

SELECT DATE_FORMAT('1999-01-01', '%X %V');

-> '1998 52'

mysql>

SELECT DATE_FORMAT('2006-06-00', '%d');

-> '00'

oracle ：ROUND ，TRUNC，TO_CHAR 分割时间查询。