Queries with hours in SQL Server?

Question

Queries with hours in SQL Server?

Navigation

#1 by (4 votes)
#2 by (2 votes)

5

I have a question about how I can make queries in SQL Server with hours fields.

I have a column called hora_inicio_prestamo and another hora_fin_prestamo , both of type time(0) , I also have a column called Estado_prestamo what I need is to separate the hours between those hours and say if they are on loan .

Example:

________________________________________________________________
|Hora_inicio_prestamo   |Hora_fin_prestamo | Estado_prestamo  |
----------------------------------------------------------------
|7:00                   |  11:00             | EN PRESTAMO    |
----------------------------------------------------------------

(You could make temporary fields like this)

Hora  |   Estado
7:00 ---> EN PRESTAMO
8:00 ---> EN PRESTAMO
9:00 ---> EN PRESTAMO
10:00 ---> EN PRESTAMO
11:00 ---> EN PRESTAMO
12:00 ---> LIBRE
13:00 ---> LIBRE

I tried to create dynamic fields with CAST , like this:

SELECT Id_prestamo_laboratorio
    ,CAST('' AS TIME(0)) AS Siete
    ,CAST('' AS TIME(0)) AS Ocho
    ,CAST('' AS TIME(0)) AS Nueve
FROM PrestamoLaboratorios

sql sql-server tsql

asked by JDiego9708 07.04.2017 в 03:05

source

2 answers

2

This is only a complement to Elena López's response, which I think is excellent, you just had to correct a couple of things in your code:

The end time always remained as 23:00:00 , adjusted with SET @HoraFin = dateadd(hour, 1, @HoraInicio)
Within WHEN the condition AND HoraFin > @HoraInicio is corrected, previously it had >= , which caused that the schedule extended one hour more.

Demonstration of the solution.

Results:

+------------+----------+----------+-------------+
| HoraInicio | HoraFin  | Estado   |             |
+------------+----------+----------+-------------+
| 1          | 07:00:00 | 13:00:00 | En préstamo |
+------------+----------+----------+-------------+
| 2          | 08:00:00 | 09:00:00 | En préstamo |
+------------+----------+----------+-------------+
| 3          | 09:00:00 | 10:00:00 | En préstamo |
+------------+----------+----------+-------------+
| 4          | 10:00:00 | 11:00:00 | En préstamo |
+------------+----------+----------+-------------+
| 5          | 11:00:00 | 12:00:00 | Libre       |
+------------+----------+----------+-------------+
| 6          | 12:00:00 | 13:00:00 | Libre       |
+------------+----------+----------+-------------+
| 7          | 13:00:00 | 14:00:00 | Libre       |
+------------+----------+----------+-------------+
| 8          | 14:00:00 | 15:00:00 | Libre       |
+------------+----------+----------+-------------+

answered by 07.04.2017 в 19:09

Change color of an image with css How to validate special characters in a text field

score 4 · Accepted Answer

Part of this code

create table #Prestamos (HoraInicio time, HoraFin time, Estado Varchar(11))
insert into #Prestamos (HoraInicio, HoraFin) values ('7:00', '11:00')

declare @Contador tinyint = 24,
    @HoraInicio time = '00:00:00.0000000',
    @HoraFin time = '23:00:00.0000000'

while @Contador > 0
begin
    insert into #Prestamos (HoraInicio, HoraFin, Estado)
select  top 1
        @HoraInicio,
        @HoraFin,
        case
            when HoraInicio <= @HoraInicio  and HoraFin >= @HoraInicio
             then 'En préstamo'
          else 'Libre'
        end as Estado
from #Prestamos 

set @HoraInicio = dateadd(hour, 1, @HoraInicio)
set @Contador -= 1
select @Contador, @HoraInicio, @HoraFin

end

select  HoraInicio, 
    HoraFin, 
    Estado
from #Prestamos