MSSQL 日期與時間處理方法
[b]取得目前時間（日期時間型態資料）[/b]select getdate()
[b]取得 YY/MM/DD（字串型態資料）[/b]
select convert(char,getdate(),11)
[b]取得 YYYY/MM/DD（字串型態資料）[/b]
select convert(char,getdate(),111)
[b]取得 YYMMDD（字串型態資料）[/b]
select convert(char,getdate(),12)
[b]取得 YYYYMMDD（字串型態資料）[/b]
select convert(char,getdate(),112)
[b]取得 YYYYMMDD（日期時間型態資料）[/b]
select dateadd(dd,datediff(dd,0,getdate()),0)
[b]取得星期幾的單一中文字(日,一,二,三,四,五,六)[/b]
SELECT Right(datename(weekday,getdate()),1) --系統地區語言必須為中文地區
SELECT CASE datepart(dw, getdate())
WHEN 1 THEN '日'
WHEN 2 THEN '一'
WHEN 3 THEN '二'
WHEN 4 THEN '三'
WHEN 5 THEN '四'
WHEN 6 THEN '五'
WHEN 7 THEN '六' END 參考來源 [url]http://ithelp.ithome.com.tw/question/10018369[/url][code]Create Procedure dbo.uspGetWorkingDay
@date1 datetime
as
--取傳入的日期之後扣掉(六)(日)的第四天
declare @counter1 int
declare @day_count int
select @counter1 = 0
select @day_count= 4
while @counter1 < @day_count
begin
if (datepart(weekday,dateadd(dd,+1,@date1)) != 1 AND datepart(weekday,dateadd(dd,+1,@date1)) != 7)
select @counter1 = @counter1 + 1
select @date1 = dateadd(dd,+1,@date1)
end
select workingday = @date1
go
exec uspGetWorkingDay '2008/01/01'
[/code] 產生日期列表[code]
declare @datelist table(datelist datetime)
declare @sdate datetime
declare @edate datetime
select @sdate = '2010/01/01'
select @edate = '2010/12/31'
while @sdate <= @edate
begin
insert into @datelist select @sdate
set @sdate = dateadd(dd,1,@sdate)
end
select * from @datelist
[/code]在 SQL Query Analyzer 中的執行結果：
改成使用者自定函數[code]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uf_getDateList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[uf_getDateList]
GO
create function [dbo].[uf_getDateList](
@sdate datetime,
@edate datetime
) returns @datelist table(datelist datetime)
as begin
while @sdate <= @edate
begin
insert into @datelist select @sdate
set @sdate = dateadd(dd,1,@sdate)
end
return
end
GO
[/code]就可以用下列SQL方便使用[code]select * from uf_getDateList('2010/06/01','2010/6/30')[/code]
頁:
[1]