計算年資(几年几月几日)的SQL語句
来源:互联网 发布:php namespace use as 编辑:程序博客网 时间:2024/04/29 23:51
首先感謝:bzscs(沙虫 我爱小美) 和lxzm1001(*悠悠蓝星*)
偶的問題貼:http://community.csdn.net/Expert/topic/5009/5009969.xml?temp=.5809442
1.bzscs(沙虫 我爱小美)用函數的好辦法:
CREATE function [dbo].[calc_date](@time smalldatetime,@now smalldatetime)
returns nvarchar(10)
as
begin
declare @year int,@month int,@day int
select @year = datediff(yy,@time,@now)
if (month(@now)=month(@time)) and (day(@now)<day(@time)) or (month(@now)<month(@time))
set @year = @year-1
select @month = datediff(month,@time,@now)-12*@year
if(day(@now)<day(@time))
set @month = @month-1
select @day = datediff(dd,dateadd(month,(12*@year+@month),@time),@now)
return cast(@year as varchar) + '年'+ cast(@month as varchar)+'個月'+cast(@day as varchar)+'天'
end
GO
-------------------------------------------------------------------------------------
--調用函數 :
--1.某一具體日期
declare @kk nvarchar(10)
select @kk = [dbo].[calc_date]('2001-11-28',getdate())
print @kk
--2.數據表的'入職日期'字段
select empno,indate,[dbo].[calc_date](indate,getdate()) from employee
----------------------------------------------------------------------------------
returns nvarchar(10)
as
begin
declare @year int,@month int,@day int
select @year = datediff(yy,@time,@now)
if (month(@now)=month(@time)) and (day(@now)<day(@time)) or (month(@now)<month(@time))
set @year = @year-1
select @month = datediff(month,@time,@now)-12*@year
if(day(@now)<day(@time))
set @month = @month-1
select @day = datediff(dd,dateadd(month,(12*@year+@month),@time),@now)
return cast(@year as varchar) + '年'+ cast(@month as varchar)+'個月'+cast(@day as varchar)+'天'
end
GO
-------------------------------------------------------------------------------------
--調用函數 :
--1.某一具體日期
declare @kk nvarchar(10)
select @kk = [dbo].[calc_date]('2001-11-28',getdate())
print @kk
--2.數據表的'入職日期'字段
select empno,indate,[dbo].[calc_date](indate,getdate()) from employee
----------------------------------------------------------------------------------
2.自己的笨辦法:新增了個表,還用了游標
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmpemp]
GO
CREATE TABLE [dbo].[tmpemp] (
[empno] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[indate] [datetime] NULL ,
[timelong] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmpemp] WITH NOCHECK ADD
CONSTRAINT [PK_tmpemp] PRIMARY KEY CLUSTERED
(
[empno]
) ON [PRIMARY]
GO
insert into tmpemp SELECT empno,indate,'' from employee where flag=1
declare @year int,@month int,@day int,@emp_no char(8),@time datetime
declare cur02 cursor for
select empno,indate
from tmpemp
open cur02
fetch next from cur02 into @emp_no,@time
while @@fetch_status<>-1
begin
select @year = datediff(yy,@time,getdate())
if (month(getdate())=month(@time)) and (day(getdate())<day(@time)) or (month(getdate())<month(@time))
set @year=@year-1
select @month = datediff(month,@time,getdate())-12*@year
if (day(getdate())<day(@time))
set @month = @month-1
select @day = datediff(dd,dateadd(month,(12*@year+@month),@time),getdate())
print @emp_no+' ' +convert(varchar,@time,111) +' ' +cast(@year as varchar) + '年'+ cast(@month as varchar)+'個月'+cast(@day as varchar)+'天'
update tmpemp set timelong=cast(@year as varchar) + '年'+ cast(@month as varchar)+'個月'+cast(@day as varchar)+'天' where empno=@emp_no
fetch next from cur02 into @emp_no,@time
end
close cur02
deallocate cur02
--調用--
select empno,indate,timelong as 年資 from tmpemp
drop table [dbo].[tmpemp]
GO
CREATE TABLE [dbo].[tmpemp] (
[empno] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[indate] [datetime] NULL ,
[timelong] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmpemp] WITH NOCHECK ADD
CONSTRAINT [PK_tmpemp] PRIMARY KEY CLUSTERED
(
[empno]
) ON [PRIMARY]
GO
insert into tmpemp SELECT empno,indate,'' from employee where flag=1
declare @year int,@month int,@day int,@emp_no char(8),@time datetime
declare cur02 cursor for
select empno,indate
from tmpemp
open cur02
fetch next from cur02 into @emp_no,@time
while @@fetch_status<>-1
begin
select @year = datediff(yy,@time,getdate())
if (month(getdate())=month(@time)) and (day(getdate())<day(@time)) or (month(getdate())<month(@time))
set @year=@year-1
select @month = datediff(month,@time,getdate())-12*@year
if (day(getdate())<day(@time))
set @month = @month-1
select @day = datediff(dd,dateadd(month,(12*@year+@month),@time),getdate())
print @emp_no+' ' +convert(varchar,@time,111) +' ' +cast(@year as varchar) + '年'+ cast(@month as varchar)+'個月'+cast(@day as varchar)+'天'
update tmpemp set timelong=cast(@year as varchar) + '年'+ cast(@month as varchar)+'個月'+cast(@day as varchar)+'天' where empno=@emp_no
fetch next from cur02 into @emp_no,@time
end
close cur02
deallocate cur02
--調用--
select empno,indate,timelong as 年資 from tmpemp
- 計算年資(几年几月几日)的SQL語句
- 解讀別人的SQL語句
- SQL Server的一些常用語句
- oracle 相關的sql語句
- SQL語句日期的處理
- 常用SQL語句
- 实用SQL語句
- SQL 語句
- 經典SQL語句
- 实用SQL語句
- 精妙SQL語句
- 实用SQL語句
- oracle SQL語句
- 一些SQL語句
- 精妙SQL語句
- SQL語句大全
- 得到SQL SERVER DB SCHEMA 的SQL語句
- 一句集大成的SQL
- 存储过程编写经验和优化措施
- 初尝lamp
- c# 将图片的二进制数据插入到数据库中
- Linux系统下的多线程编程入门
- SSL加强数据传输安全
- 計算年資(几年几月几日)的SQL語句
- 还原DXT1格式的DDS文件(S3TC系列1)
- c# 控制输入框的输入为数字
- xslt 排序、遍历、原码输出
- 相对论学习和思考 - Part 2(Terminate)
- Flexlm SDK 9.2 编译方法
- java的事件处理机制
- 在C#里把ArrayList转换为Array 或 把Array转换为ArrayList
- 金旭亮老师《.NET》免费公开课公告