第二章 单表查询(3)

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]

--CastºÍConvert Á½¸öº¯Êý¶¼¿ÉÒÔ½«ÊäÈëµÄֵת»»ÎªÖ¸¶¨µÄÊý¾ÝÀàÐÍ,CastÊÇANSI±ê×¼SQL£¬¶øConvert²»ÊÇ
select CONVERT(char(50), current_timestamp) as [Date]

--dateaddº¯Êý ¿ÉÒÔ½«Ö¸¶¨ÈÕÆڵIJ¿·Ö×÷Ϊµ¥Î»£¬ÎªÊäÈëµÄÈÕÆÚºÍʱ¼äÖµÔö¼ÓÖ¸¶¨µÄÊýÁ¿
select DATEADD(year, 1 , current_timestamp) as [One Year's Later]

--datediffº¯Êý ·µ»ØÁ½¸öÈÕÆÚºÍʱ¼äÖµÖ®¼äÏà²îµÄÖ¸¶¨²¿·ÖµÄ¼ÆÊý
select datediff(day, N'20090720', current_timestamp) as [Work Days]

--»ñÈ¡µ±Ç°ÔµÄ×îºóÒ»Ìì
select dateadd(month,datediff(month, N'19991231', current_timestamp),N'19991231') 
as [Last Month Day]

--DatePartº¯Êý·µ»ØÒ»¸ö±íʾ¸ø¶¨ÈÕÆÚºÍʱ¼äÖµµÄÖ¸¶¨²¿·ÖµÄÕûÊý
select DATEPART(year, current_timestamp)

--year month dayº¯ÊýÊÇDatePartº¯ÊýµÄ¼òÂÔ°æ±¾

--isdateº¯Êý ½ÓÊÜÒ»¸ö×Ö·û´®×÷ΪÊäÈ룬Èç¹ûÄÜ°ÑÕâ¸ö×Ö·û´®×ª»»ÎªÈÕÆÚºÍʱ¼äÊý¾ÝÀàÐ͵ÄÖµ£¬Ôò·µ»Ø1£»Èç¹û²»ÄÜ£¬Ôò·µ»Ø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'