如何将一列数据库拆分为多列，并将date-time数据类型的值与string数据类型进行比较

I have two different MS excel sheets containing 40000 rows of data having around 12 columns.I would like to import the data from both the sheets in a database(any database can be used) into 2 different tables(table_a & table_b). Now, there is column(with column name: "start_time") in 'table_a' with the datatype 'date/time' and the value stored is "08:30:50". Also, there is a column(with column name: "total_time") in 'table_b' with the datatype 'string' and the value stored is "0800-1000".

我有两个不同的MS excel表,包含大约12列的40000行数据。我想将数据库中的两个表(可以使用任何数据库)中的数据导入到2个不同的表(table_a和table_b)中。现在,'table_a'中有一列(列名:“start_time”),数据类型为“date / time”,存储的值为“08:30:50”。此外,'table_b'中有一列(列名:“total_time”),数据类型为'string',存储的值为“0800-1000”。

My question here is, by looking the value "08:30:50" I understand that it falls in the range "0800-1000". But how can do this with a sql query considering that I will be using MS SQL 2005 or MS SQL 2008?

我的问题是,通过查看值“08:30:50”,我知道它属于“0800-1000”范围。但是考虑到我将使用MS SQL 2005或MS SQL 2008,如何通过SQL查询来做到这一点?

Thanks in advance.

提前致谢。

1 个解决方案

#1

this could be the solution :-

这可能是解决方案: -

Set Nocount On;

Declare @table_a Table
(
     Id         Int Identity(1,1)
    ,starttime  Datetime
)

Declare @table_b Table
(
     Id         Int Identity(1,1)
    ,total_time Varchar(100)
)

Insert Into @table_a(starttime) Values
 ('08:30:50')
,('06:30:50')

Insert Into @table_b(total_time) Values
 ('0600-0759')
,('0800-1000')

Select   a.Id
        ,a.starttime
        ,b.total_time
From    (
            Select   Id
                    ,b.total_time
                    ,Cast(Left(b.total_time,2) +':'+ Right(Left(b.total_time,4),2) As time) As StartHours
                    ,Cast(Left(Right(b.total_time,4),2) +':'+ Right(b.total_time,2) As time) As EndHours
            From    @table_b As b
        ) As b
        Join @table_a As a On Cast(a.starttime As Time) >= b.StartHours And Cast(a.starttime As Time) <= b.EndHours

Output :-