获取日期型字段的年,月或日的函数是什么?(50分)

Z

zzx8866

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-24

• #1

在query.sql中在如下语句:updat customer set customer.年月=year(customer.日期)+
month(customer.日期)*0.01 where day(customer.日期)<26
其中 "年月"为数值类型,"日期"为date型.运行时系统报错:不能识别 year,mouth,day.
我把这三个函数改为以下形式:TO_INTEGER(CUSTOMER.日期,'YYYY'),
TO_INTEGER(CUSTOMER.日期,'MM').TO_INTEGER(CUSTOMER.日期,'DD')后,系统又显示数据库
引擎不支持.我找不到合适的函数来替换.敬请专家指点.

 

J

jqw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-24

• #2

in delphi:
decode()
month()
year()
day()
then, you can group your sql

 

R

R_Baggio

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-24

• #3

decodetime

 

I

iie

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-24

• #4

用decode

 

Z

zzx8866

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-24

• #5

其实我意思是sql中如何获取日期字段的年,月,日的函数,以上三位朋友提出的函数是不能
直接嵌在QUERY.SQL中的.

 

Q

qian

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-24

• #6

select datepart(yy,'2000/5/2')
select datepart(mm,'2000/5/2')
select datepart(dd,'2000/5/2')

 

W

wumeng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-24

• #7

g:qian

datepart
Is a parameter used with DATEADD, DATEDIFF, DATENAME, and DATEPART.
The following table lists the date parts, the abbreviations recognized
by SQL Server, and the acceptable values:


Date part Abbreviation Values
year yy 1753-9999
quarter qq 1-4
month mm 1-12
day of year dy 1-366
day dd 1-31
week wk 1-53
weekday dw 1-7 (Sun.-Sat.)
hour hh 0-23
minute mi 0-59
second ss 0-59
millisecond ms 0-999

 

Z

zzx8866

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-24

• #8

pdate bdata
set bdata.年月=datepart(yy,bdata.日期)+datepart(mm,BDATA.日期)*0.01
where datepart(dd,BDATA.日期)<=25

系统还是显示"CAPAbility not surported"

 

P

Pipi.

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-24

• #9

和数据库有关，你的是什么数据库

 

Z

zzx8866

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-25

• #10

是paradox(.db)数据库

 

P

Pipi.

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-25

• #11

那就是用local sql 了：

SELECT SaleDate,

EXTRACT(YEAR FROM saledate) AS YY,
EXTRACT(MONTH FROM saledate) AS MM,
EXTRACT(DAY FROM saledate) AS DD

FROM Orders

 

P

Pipi.

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-25

• #12

EXTRACT(YEAR FROM saledate) 从 caledate 取出 年 部分
EXTRACT(MONTH FROM saledate) 从 caledate 取出 月 部分
EXTRACT(DAY FROM saledate) 从 caledate 取出 日 部分

 

Z

zzx8866

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-07-26

• #13

接受答案了.