常见的Sql时间函数更新中
-- MSSQL 提供了这样的函数
-- 提出年份
select datepart(year, getdate())
-- 提出月份
select datepart(month, getdate())
-- 提出星期(从年初始到当前日期)
select datepart(week, getdate())
-- 提出天数(当月第几天)
select datepart(day, getdate())
-- 提取天数(从年初起到当前日期)
select datepart(dayofyear, getdate())
-- 提取小时
select datepart(hour, getdate())
-- 提取分钟
select datepart(minute, getdate())
-- 提取秒钟
select datepart(second, getdate())
-- 提取毫秒
select datepart(millisecond, getdate())
select * from(select Convert(varchar(10),M.FLD0050,120)AS MMON fromVR_Master_Info AS M) AS MMMM
where MMON='2010'
select * from(select datepart(month, M.FLD0050)
AS MMON from VR_Master_Info AS M) AS MMMM
where MMON='5'
方法dateadd(m,6, M.FLD0050) 将字段FLD0050加上6个月
或
方法dateadd(month,6, M.FLD0050) 将字段FLD0050加上6个月
方法dateadd(day,6, M.FLD0050) 将字段FLD0050加上6天
方法dateadd(year,1, M.FLD0050) 将字段FLD0050加上1年
select dateadd(m,6, M.FLD0050)
as moodo ,datepart(month, M.FLD0050)
as MMON ,M.FLD0620 from VR_Master_Info AS M
方法datepart(month, dateadd(m,6, M.FLD0050)) 将字段FLD0050加上6个月后截取月份
select * from (select datepart(month, dateadd(m,6, M.FLD0050)
) as moodo ,datepart(month, M.FLD0050)
as MMON ,M.FLD0620 from VR_Master_Info as M) as VRM
where (moodo='1' or MMON='1' ) and FLD0620=0
--一年审
SELEct * from (select datepart(m, dateadd(year,1, M.FLD0050)
) as add6month ,datepart(month, M.FLD0050)
AS oldmonth ,M.FLD0010,M.FLD0020,M.FLD0030,M.FLD0040,M.FLD0050, M.FLD0060,M.FLD0070,M.FLD0080,M.FLD0090,M.FLD0100,M.FLD0110,M.FLD0120,M.FLD0130,M.FLD0140,M.FLD0150,
M.FLD0160,M.FLD0170,M.FLD0180,M.FLD0190,M.FLD0200,M.FLD0210,M.FLD0220,M.FLD0230,M.FLD0240,M.FLD0250,M.FLD0260,M.FLD0270,M.FLD0280,M.FLD0290,M.FLD0300,
M.FLD0310,M.FLD0320,M.FLD0330,M.FLD0340,M.FLD0350,M.FLD0360,M.FLD0370,M.FLD0380,M.FLD0390,M.FLD0400,M.FLD0410,M.FLD0420,M.FLD0430,M.FLD0440,M.FLD0450,M.FLD0460,M.FLD0470,
M.FLD0480,M.FLD0490,M.FLD0500,M.FLD0510,M.FLD0520,M.FLD0530,M.FLD0540,M.FLD0550,M.FLD0560,M.FLD0570,M.FLD0580,M.FLD0590,M.FLD0600,M.FLD0610,M.FLD0620
from VR_Master_Info AS M) AS VRM where oldmonth =3 and FLD0550=1 and (FLD0610 IN (5,7,9) )
and FLD0620=1 order by FLD0050 ASC
((year(convert(datetime, '2012-1-1'))-year(FLD0050))%2) as twoyear
--二年审
SELEct * from (select ((year(convert(datetime, '2012-1-1'))-year(FLD0050))%2) as twoyear,M.FLD0010,M.FLD0020,M.FLD0030,M.FLD0040,M.FLD0050, M.FLD0060,M.FLD0070,M.FLD0080,M.FLD0090,M.FLD0100,M.FLD0110,M.FLD0120,M.FLD0130,M.FLD0140,M.FLD0150,
M.FLD0160,M.FLD0170,M.FLD0180,M.FLD0190,M.FLD0200,M.FLD0210,M.FLD0220,M.FLD0230,M.FLD0240,M.FLD0250,M.FLD0260,M.FLD0270,M.FLD0280,M.FLD0290,M.FLD0300,
M.FLD0310,M.FLD0320,M.FLD0330,M.FLD0340,M.FLD0350,M.FLD0360,M.FLD0370,M.FLD0380,M.FLD0390,M.FLD0400,M.FLD0410,M.FLD0420,M.FLD0430,M.FLD0440,M.FLD0450,M.FLD0460,M.FLD0470,
M.FLD0480,M.FLD0490,M.FLD0500,M.FLD0510,M.FLD0520,M.FLD0530,M.FLD0540,M.FLD0550,M.FLD0560,M.FLD0570,M.FLD0580,M.FLD0590,M.FLD0600,M.FLD0610,M.FLD0620
from VR_Master_Info AS M) AS VRM where twoyear=0 and FLD0550=1 and (FLD0610 IN (5,7,9) )
and FLD0620=1 order by FLD0050 ASC
--半年审
SELEct * from (select datepart(month, dateadd(m,6, M.FLD0050)
) as add6month ,datepart(month, M.FLD0050)
AS oldmonth ,M.FLD0010,M.FLD0020,M.FLD0030,M.FLD0040,M.FLD0050, M.FLD0060,M.FLD0070,M.FLD0080,M.FLD0090,M.FLD0100,M.FLD0110,M.FLD0120,M.FLD0130,M.FLD0140,M.FLD0150,
M.FLD0160,M.FLD0170,M.FLD0180,M.FLD0190,M.FLD0200,M.FLD0210,M.FLD0220,M.FLD0230,M.FLD0240,M.FLD0250,M.FLD0260,M.FLD0270,M.FLD0280,M.FLD0290,M.FLD0300,
M.FLD0310,M.FLD0320,M.FLD0330,M.FLD0340,M.FLD0350,M.FLD0360,M.FLD0370,M.FLD0380,M.FLD0390,M.FLD0400,M.FLD0410,M.FLD0420,M.FLD0430,M.FLD0440,M.FLD0450,M.FLD0460,M.FLD0470,
M.FLD0480,M.FLD0490,M.FLD0500,M.FLD0510,M.FLD0520,M.FLD0530,M.FLD0540,M.FLD0550,M.FLD0560,M.FLD0570,M.FLD0580,M.FLD0590,M.FLD0600,M.FLD0610,M.FLD0620
from VR_Master_Info AS M) AS VRM
where (add6month = 9 or oldmonth =9 ) and FLD0550=1 and (FLD0610 IN (1,5,9) ) and FLD0620=0 order by FLD0050 ASC
