第二章 单表查询(3)
use TSQLFundamentals2008;go--Datetime, SmallDatetime, Date, Time, DateTime2, DateTimeOffsetselect orderid, custid, empid, orderdatefrom Sales.Orderswhere orderdate = CAST('20070212' as datetime)--Year, Monthselect orderid, custid, empid, orderdatefrom Sales.Orderswhere YEAR(orderdate)=2007 and MONTH(orderdate)=2--Get Current Timeselect 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?·?μ???êyselect 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?·?μ???êyselect DATEPART(year, current_timestamp)--year month dayoˉêyê?DatePartoˉêyμ??ò??°?±?--isdateoˉêy ?óêüò???×?·?′®×÷?aê?è?￡?è?1??ü°??a??×?·?′®×a???aè??úoíê±??êy?YààDíμ??μ￡??ò·μ??1￡?è?1?2??ü￡??ò·μ??0select ISDATE(N'99991231') --1select ISDATE(N'20120230') --0--sys.tables视图select SCHEMA_NAME(SCHEMA_ID) as table_schema_name, name as table_namefrom sys.tables--sys.columns视图select name as column_name, TYPE_NAME(system_type_id) as column_type,max_length,COLLATION_NAME,is_nullablefrom sys.columnswhere object_id = object_id(N'Sales.Orders')--sp_tables存储过程返回可以在当前数据库中查询的对象列表exec sys.sp_tables--sp_help接受一个对象名称作为输入，返回与之相关的多个结果集exec sys.sp_help @objname = N'Sales.Orders'