use TSQLFundamentals2008;
go

--Datetime, SmallDatetime, Date, Time, DateTime2, DateTimeOffset
select orderid, custid, empid, orderdate
from Sales.Orders
where orderdate = CAST('20070212' as datetime)

--Year, Month
select orderid, custid, empid, orderdate
from Sales.Orders
where YEAR(orderdate)=2007 and MONTH(orderdate)=2

--Get Current Time
select 
GETDATE() as [GetDate],
CURRENT_TIMESTAMP as [Current_TimeStamp],
GETUTCDATE() as [GetUTCDate],
SYSDATETIME() as [SysDatetime],
SYSUTCDATETIME() as [SysUTCDatetime],
SYSDATETIMEOFFSET () as [SysDatetimeOffset]

select 
month(CAST(CURRENT_TIMESTAMP as datetime)) as [CurrentMonth],
DAY(CAST(current_timestamp as datetime)) as [CurrentDay]

--CastoíConvert á???oˉêy???éò???ê?è?μ??μ×a???a???¨μ?êy?YààDí,Castê?ANSI±ê×?SQL￡???Convert2?ê?
select CONVERT(char(50), current_timestamp) as [Date]

--dateaddoˉêy ?éò??????¨è??úμ?2?·?×÷?aμ￥??￡??aê?è?μ?è??úoíê±???μ???ó???¨μ?êyá?
select DATEADD(year, 1 , current_timestamp) as [One Year's Later]

--datediffoˉêy ·μ??á???è??úoíê±???μ?®???à2?μ????¨2?·?μ???êy
select datediff(day, N'20090720', current_timestamp) as [Work Days]

--??è?μ±?°??μ?×?oóò?ìì
select dateadd(month,datediff(month, N'19991231', current_timestamp),N'19991231') 
as [Last Month Day]

--DatePartoˉêy·μ??ò???±íê????¨è??úoíê±???μμ????¨2?·?μ???êy
select DATEPART(year, current_timestamp)

--year month dayoˉêyê?DatePartoˉêyμ??ò??°?±?

--isdateoˉêy ?óêüò???×?·?′®×÷?aê?è?￡?è?1??ü°??a??×?·?′®×a???aè??úoíê±??êy?YààDíμ??μ￡??ò·μ??1￡?è?1?2??ü￡??ò·μ??0
select ISDATE(N'99991231') --1
select ISDATE(N'20120230') --0

--sys.tables视图
select SCHEMA_NAME(SCHEMA_ID) as table_schema_name, name as table_name
from sys.tables

--sys.columns视图
select name as column_name, 
TYPE_NAME(system_type_id) as column_type,
max_length,
COLLATION_NAME,
is_nullable
from sys.columns
where object_id = object_id(N'Sales.Orders')

--sp_tables存储过程返回可以在当前数据库中查询的对象列表
exec sys.sp_tables

--sp_help接受一个对象名称作为输入，返回与之相关的多个结果集
exec sys.sp_help @objname = N'Sales.Orders'

1楼mazhaojuan5天前 17:14

这是笔记，怎么没有文字说明？