实用SQL语句精选—日期函数

SELECT CAST('2003-05-08 12:35:29.998' AS smalldatetime);

SELECT CAST('2003-05-08 12:35:29.999' AS smalldatetime);

select cast(12 as char(2))+cast(13 as char(2))

select DATEPART("yy",GETDATE())   --返回日期中的年数

select DATEPART("mm",GETDATE())

select DATEPART("dd",GETDATE())

select DATEPART("hh",GETDATE())

select DATEPART("n",GETDATE())     --返回日期中的分钟数

select cast(DATEPART("hh",'2003-05-08 20:35:29.999') as char(2))+':'+cast(DATEPART("n",GETDATE()) as char(2))

 

select year(getdate())

select month(getdate())

select day(getdate())

select getdate()

select left(getdate(),10)

select right(getdate(),8)

select left(CAST(getdate() AS smalldatetime),10)

select right(CAST(getdate() AS smalldatetime),8)

select DATEDIFF("dd", '8/1/2003', '8/8/2003')     --计算间隔天数

select DATEDIFF("mm", '3/1/2011',GETDATE())       --返回间隔月数

select DATEDIFF("yy", '3/1/2010',GETDATE())       --返回间隔月数

select DATEDIFF("week", '7/1/2011',GETDATE())     --返回间隔周数

select DATEDIFF("hour", '7/21/2011',GETDATE())    --返回间隔月数

select DATEDIFF("dayofyear", '7/1/2011',GETDATE())    --返回间隔天数

select DATEDIFF("weekday", '7/1/2011',GETDATE())      --返回间隔天数

select DATEDIFF("minute", '7/21/2011',GETDATE())  

select DATEDIFF("second", '7/21/2011',GETDATE())

SET DATEFORMAT mdy;     --设置日期格式为mdy

SET DATEFORMAT ymd;

declare @start smalldatetime

select @start=cast(cast(DATEPART("yy",GETDATE()) as char(4))+'-'+cast(DATEPART("mm",GETDATE()) as char(2))+'-'+cast(DATEPART("dd",GETDATE()) as char(2))+' '+starttime as smalldatetime) from userpathinfo where pathid= 564

select @start

select dateadd(month,2,@start)

 

SELECT DATENAME(month, GETDATE()) AS 'Month Name'

SELECT DATENAME(weekday, GETDATE())   --返回星期名称，如星期三

 

select dateadd(month,-1,getdate())     --在向指定日期加上一段时间的基础上，返回新的datetime 值

select replace('大连XXXX公司大连XXXX办事处','大连','北京')

select convert(varchar,getdate(),112)   -- 如：20110902

 

select cast(cast(DATEPART("yy",GETDATE()) as char(4))+'-'+cast((DATEPART("mm",GETDATE())-1) as char(2))+'-'+cast(1 as char(2)) as smalldatetime)

if 5!=4

print 'y > z'

else 

print 'y < z'

 

--获得有关存储过程的信息

--若要显示Transact-SQL 存储过程的定义，请使用该过程所在的数据库中的sys.sql_modules 目录视图。

USE AdventureWorks;

GO

SELECT definition

FROM sys.sql_modules

JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

 

exec getalluserinfo    --执行存储过程

EXECUTE HumanResources.usp_GetEmployees 'Ackerman', 'Pilar';

-- Or

EXEC HumanResources.usp_GetEmployees @lastname = 'Ackerman', @firstname = 'Pilar';