任职时间和离职时间都有，怎么统计工作天数（离职时间-任职时间）

任职时间和离职时间都有，如何统计工作天数（离职时间-任职时间）
select xingming,ruzhishijian,lizhishijian from d_yuangong


===============================
序号 姓名 入职时间 离职时间  
   
1 张三 2012-4-21 8:30:00 2012-5-21 8:30:00




我想要的效果
===================
序号 姓名 入职时间 离职时间 在职天数
   
1 张三 2012-4-21 8:30:00 2012-5-21 8:30:00 30

[解决办法]
datediff(day,入职时间 ,离职时间)
[解决办法]
datediff函数
[解决办法]
datediff(dd,time1,time2)
[解决办法]

SQL code

select xingming,ruzhishijian,lizhishijian,datediff(day,ruzhishijian ,lizhishijian) as '在职天数' from d_yuangong
[解决办法]
报什么错?
探讨

语句通不过。

[解决办法]
还要去除国家规定的假日,以及这个企业是否有双休日.
[解决办法]
一般而言，人事管理系统里应该有一张表，存放当年的法定节假日、双休日，然后结合这样表进行计算。
建议楼主先建立这样的一张表，然后进行计算。
[解决办法]
SQL code
select xingming as 姓名,ruzhishijian as 入职时间,lizhishijian as 离职时间,datediff(day,ruzhishijian,lizhishijian) as 在职天数 from d_yuangong
[解决办法]
根据姓名排序得到序号。
SQL code
select ROW_NUMBER OVER(PARTITION BY xingming) AS 序号,xingming AS 姓名,ruzhishijian AS 入职时间,lizhishijian AS 离职时间,DATEDIFF(DAY,CAST(ruzhishijian AS DATE), CAST(lizhishijian AS DATE)) AS 在职天数 from d_yuangong
[解决办法]
不好意思啊 楼主，上面代码错了，试试底下的吧
SQL code
select ROW_NUMBER() OVER(order BY xingming) AS 序号,xingming AS 姓名,ruzhishijian AS 入职时间,lizhishijian AS 离职时间,DATEDIFF(DAY,CAST(ruzhishijian AS DATE), CAST(lizhishijian AS DATE)) AS 在职天数 from d_yuangong
[解决办法]
SQL code
select ROW_NUMBER() OVER(order BY xingming) AS 序号,xingming AS 姓名,ruzhishijian AS 入职时间,lizhishijian AS 离职时间,DATEDIFF(DAY,CAST(ruzhishijian AS DATE), CAST(lizhishijian AS DATE)) AS 在职天数 from d_yuangong