SQL Server的日期计算

a. 一个月的第一天

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

b. 本周的星期一

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

c. 一年的第一天

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

d. 季度的第一天

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

e. 上个月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

f. 去年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

g. 本月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

h. 本月的第一个星期一

select DATEADD(wk, DATEDIFF(wk,0,

dateadd(dd,6-datepart(day,getdate()),getdate())

), 0)

i. 本年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。

获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]

SELECT CASE IsNull(I.name, '')

When '' Then ''

Else '*'

End as IsPK,

Object_Name(A.id) as t_name,

A.name as c_name,

IsNull(SubString(M.text, 1, 254), '') as pbc_init,

T.name as F_DataType,

CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')

WHEN '' Then Cast(A.prec as varchar)

ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar)

END as F_Scale,

A.isnullable as F_isNullAble

FROM Syscolumns as A

JOIN Systypes as T

ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') )

LEFT JOIN ( SysIndexes as I

JOIN Syscolumns as A1

ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )

ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) )

LEFT JOIN SysComments as M

ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )

ORDER BY A.Colid ASC