如何生成给定时间的日期? [英] How to generate a date for the given time?

问题描述

使用 VB.NET 和 SQL Server 2005

Using VB.NET and SQL Server 2005

我想根据给定的 Outtime 生成一个日期，

I want to generate a date according to the given Outtime,

我正在输入类似的值

ID  Date       Intime   Outtime  Holiday

001 23-02-2009 08:00:00 17:00:00 no
001 24-02-2009 17:00:00 08:00:00 no
001 25-02-2009 10:00:00 16:00:00 no
001 26-02-2009 18:00:00 20:00:00 no
001 27-02-2009                   yes

预期输出

表 1

ID  Date       Intime   Outtime  DateIn     Dateout

001 23-02-2009 08:00:00 17:00:00 23-02-2009 23-02-2009
001 24-02-2009 17:00:00 08:00:00 24-02-2009 25-02-2009
001 25-02-2009 10:00:00 16:00:00 25-02-2009 25-02-2009
001 26-02-2009 18:00:00 20:00:00 26-02-2009 27-02-2009
001 27-02-2009     -        -        -         -

…,

从上表中，DateIn 列值应显示相同的日期列值，但 Dateout 列应根据 Outtime 显示.

From the above table, DateIn column value should display same date column value, But Dateout column should display according to the Outtime.

In a first row – Intime is 08:00:00 and Outtime is 17:00:00, So Dateout should display the same date
In a second row – Intime is 17:00:00 and Outtime is 08:00:00, So DateOut should display the next day date.
In a third row – Intime is 10:00:00 and Outtime is 16:00:00, so Dateout should display the same date
In a fourth row – Intime is 18:00:00 and Outtime is 21:00:00, so Dateout should display the next day date.
In a fifth row – Holiday column value is yes, so it should not display any value in DateIn and DateOut

Dateout 列应该比较 Intime 和 Outtime 值，如果 Outtime 小于 Intime，Dateout 列应该显示第二天的日期.

Dateout column should compare the Intime and Outtime value, if Outtime is less than Intime, Dateout column should display the next day date.

如果Holiday = Yes 那么它应该显示为一个空白列

If Holiday = Yes then It should display as a blank column

对于上述情况，如何进行sql查询?

For the above condition, How to make a sql query?

在vb.net中是否可以，哪个是更快的sql查询或vb.net代码?

Whether it is possible in vb.net, which is faster sql query or vb.net code?

上述条件需要 SQL Query 或 VB.NET 代码.

Need SQL Query or VB.NET code for the above condition.

推荐答案

这似乎有效(请注意，我的日期是美国格式，因为那是我所在的位置 :-) ):

This seems to work (note that my dates are in US format, since that's where I am :-) ):

create table times
   (
   Id             integer,
   stDate         datetime,
   InTime         datetime,
   OutTime        datetime,
   Holiday        varchar(3)
   )
go

insert into times values (001, '02-23-2009', '08:00:00', '17:00:00', 'no')
insert into times values (001, '02-24-2009', '17:00:00', '08:00:00', 'no')
insert into times values (001, '02-25-2009', '10:00:00', '16:00:00', 'no')
insert into times values (001, '02-26-2009', '21:00:00', '20:00:00', 'no')
insert into times values (001, '02-27-2009', null, null, 'yes')
go

select * from times
go

select
   t.Id,
   stDate,
   InTime,
   OutTime,
   case
      when Holiday = 'no' then stDate
      else null
   end       DateIn,
   case
      when InTime > OutTime and Holiday = 'no' then stDate + 1
      when InTime < OutTime and Holiday = 'no' then stDate
      else null
   end       DateOut
from
   times t

结果:

Id         stDate                  InTime                  OutTime                 DateIn                  DateOut                 
---------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- 
1          2009-02-23 00:00:00.000 1900-01-01 08:00:00.000 1900-01-01 17:00:00.000 2009-02-23 00:00:00.000 2009-02-23 00:00:00.000 
1          2009-02-24 00:00:00.000 1900-01-01 17:00:00.000 1900-01-01 08:00:00.000 2009-02-24 00:00:00.000 2009-02-25 00:00:00.000 
1          2009-02-25 00:00:00.000 1900-01-01 10:00:00.000 1900-01-01 16:00:00.000 2009-02-25 00:00:00.000 2009-02-25 00:00:00.000 
1          2009-02-26 00:00:00.000 1900-01-01 21:00:00.000 1900-01-01 20:00:00.000 2009-02-26 00:00:00.000 2009-02-27 00:00:00.000 
1          2009-02-27 00:00:00.000 NULL                    NULL                    NULL                    NULL                    

5 Row(s) affected

这篇关于如何生成给定时间的日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！