sql语句(10)

5.4.1. CURRENT_TIMESTAMP 功能:

得到当前数据库的日期 代码:

//直接得到当前日期

SELECT CURRENT_TIMESTAMP go

//调用变量中的当前日期 DECLARE @today datetime

SELECT @today = current_timestamp select @today go

----------------------------------------------------------- 5.4.2. 日期计算Date calculation 功能:日期计算 代码:

DECLARE @MonthChar VarChar(2), @DayChar VarChar(2), @DateOut Char(8)

SET @MonthChar = CAST(MONTH(GETDATE()) AS VarChar(2))

SET @DayChar = CAST(DAY(GETDATE()) AS VarChar(2))

--自动补齐月份到2位 IF LEN(@MonthChar) = 1

SET @MonthChar = '0'+@MonthChar IF LEN(@DayChar) = 1

SET @DayChar = '0' + @DayChar --生成日期字符串 SET

@DateOut

=

@MonthChar

+

@DayChar

+

CAST(YEAR(GETDATE()) AS Char(4)) SELECT @DateOut GO

运行结果是mmddyyyy格式的字符串 ----------------------------------------------------------- 5.4.3. DATEADD()

功能:日期相加或者相减n天后的日期 语

法:DATEADD(what_to_add,number_to_add,date_to_add_it_to) 代码:

//4-29-2009加90天，保存到day SELECT DATEADD(DY, 90,'4-29-2009') GO

//4-29-2009减60天，保存到day SELECT DATEADD(DY, -60,'4-29-2009')

GO

----------------------------------------------------------- 5.4.4. DATEDIFF()

功能:日期相加或者相减n天后的日期

语法:DATEDIFF ( datepart , startdate , enddate ) datepart列表: day:单位=天 month:单位=月 year:单位=年 hour:单位=小时 minute:单位=分 second:单位=秒 week:单位=周 代码:

//10/01/2009国庆到今天的天数 SELECT

DATEDIFF(day,'10/1/2009',CURRENT_TIMESTAMP) GO

//10/01/2009国庆到今天的月数 SELECT

DATEDIFF(month,'10/1/2009',CURRENT_TIMESTAMP) GO

//10/01/2009国庆到今天的年数 SELECT

DATEDIFF(year,'10/1/2009',CURRENT_TIMESTAMP) GO

//10/01/2009国庆到今天的周数 SELECT

DATEDIFF(week,'10/1/2009',CURRENT_TIMESTAMP) GO

----------------------------------------------------------- 5.4.5. DATEFIRST()

功能:设置或者查询一周的第一天

SELECT @@DATEFIRST 'First Day of the Week' GO value is 7 SELECT

DATEPART(weekday,

CAST('20091001'

DATETIME) + @@DATEFIRST); GO value is 3

----------------------------------------------------------- 6. DATEFORMAT() 功能:设置日期格式

AS

语法:SET DATEFORMAT format(ymd,mdy,dmy) 代码:set dataformat mdy

----------------------------------------------------------- 7. DATENAME()

功能:日期date按datepart风格之后变成字符串 语法:DATENAME (datepart,date) datepart

列

(day,month,year,hour,minute,second,week,weekday) 代码:

select datename(day,CURRENT_TIMESTAMP) select datename(month,CURRENT_TIMESTAMP) select datename(year,CURRENT_TIMESTAMP) select datename(hour,CURRENT_TIMESTAMP) select datename(minute,CURRENT_TIMESTAMP) select datename(week,CURRENT_TIMESTAMP) select datename(weekday,CURRENT_TIMESTAMP) ----------------------------------------------------------- 8. DATEPART()

功能:日期date按datepart风格之后变成字符串 语法:DATENAME (datepart,date) datepart

列

表

表