Based on your query, 23:35:22 should not be returned.
You're looking for values >= 24:00:00, which should never happen.
And <= 6:29:00 which would only be the early hours of a morning.
What time period are you trying to extract
Hi Gerardo,
1- There is not such time as '24:00:00'. The correct one should be '00:00:00.000'
2- You need to use same date as reference.
Select TOP 100 * from BitacoraProcesoUsuario as b
INNER JOIN Turno as t
ON b.FechaInicioProceso <= convert(char(11), b.FechaInicioProceso, 126) + '06:29:00'
I am taking off the expression:
>b.FechaInicioProceso > = convert(char(8), b.FechaInicioProceso, 112)
because any datetime value is always >= than same date from midnight.
AMB
Thanks a lot
you gave me a greate idea, the secret is in adding one day in the value fechafinproceso, this is the solution
Select
TOP 100 * from BitacoraProcesoUsuarioINNER
JOIN TurnoON
Convert(varchar, BitacoraProcesoUsuario.FechaInicioProceso, 108) >= '22:00:00' ANDConvert
(varchar, BitacoraProcesoUsuario.FechaFinProceso, 108) <= dateadd(day, 1, '06:30:00')Hi Gerardo,
Did you realize the value coming from that expression
select dateadd(day, 1, '06:30:00')
go
Result:
1900-01-02 06:30:00.000
Do you have any row where the value of BitacoraProcesoUsuario.FechaFinProceso is less than '1900-01-02 06:30:00.000'
AMB
Gerardo,
If you're trying to get all the rows between midnight and 6:30AM, may this will work:
Also, I'm not clear why/how you're joining to the Turno table.
Select
TOP 100 * from BitacoraProcesoUsuarioINNER
JOIN TurnoON
datepart(hh, BitacoraProcesoUsuario.FechaInicioProceso) < 6OR
(datepart(hh, BitacoraProcesoUsuario.FechaInicioProceso) = 6 AND datepart(mi, BitacoraProcesoUsuario.FechaInicioProceso) < 30)
Ok, you are right,
the values of the turno table are:
description start hour end hour
Matutino 06:30:00 a.m. 02:29:59 p.m. Vespertino 02:30:00 p.m. 09:59:59 p.m. Nocturno 10:00:00 p.m. 06:29:59 a.m.
my values in my table bitacoraprocesousuario are:
11:35:39 p.m. 11:39:32 p.m. 10:53:58 p.m. 10:54:13 p.m.
so, I am trying to join the hours between the start hour and the end hour of the description value "nocturno" .
Storing times in this format isn't a very good idea (storing times w/o dates is bad too, but another discussion).
Can you change how the Turno table is designed and populated
If so, then we can change those to be datetime fields and the solution becomes easier.
DaleJ:
The format of the turno table is datetime:
idturno description start hour end hour
1 | Matutino | 20/09/2006 06:30 | 20/09/2006 14:29 |
2 | Vespertino | 20/09/2006 14:30 | 20/09/2006 21:59 |
3 | Nocturno | 20/09/2006 22:00 | 20/09/2006 06:29 |
this is the layout:
IDTurno | int |
Descripcion | nvarchar |
HoraInicio | datetime |
HoraFin | datetime |
regards
Hi Gerardo,
It will be better to post the structure of the tables (DDL), including constraints and indexes, sample data in the form of "insert" statements and the expected result. That way we do not have to waste our time trying to simulate your environment. The help should be mutual, shouldn't it
create table dbo.turno ...
create table dbo.bitacora...
insert into dbo.turno(...) values(...)
...
insert into dbo.bitacora(...) values(...)
AMB
set dateformat dmy
declare @Turno table (IDTurno int identity (1,1),
Descripcion nvarchar(25),
HoraInicio datetime,
HoraFin datetime)
insert into @Turno
select 'Matutino', '01/01/1900 06:30:00', '01/01/1900 14:29:59.997'
union all select 'Vespertino', '01/01/1900 14:30:00', '01/01/1900 21:59:59.997'
union all select 'Nocturno', '01/01/1900 22:00:00', '01/01/1900 23:59:59.997'
union all select 'Nocturno', '01/01/1900 00:00:00', '01/01/1900 06:29:59.997'
select *
from @Turno
declare @bitacoraprocesousuario table (ID int identity(1,1), starttime datetime, endtime datetime)
insert into @bitacoraprocesousuario
select '20/09/2006 23:35:39', '20/09/2006 23:39:32'
union all select '14/02/2007 22:53:58', '14/02/2007 22:54:13'
union all select '01/05/2005 08:01:09', '01/05/2005 08:32:55'
union all select '04/09/2007 17:42:42', '04/09/2007 17:46:59'
union all select getdate(), getdate()+.01
select *
from @bitacoraprocesousuario
select t.Descripcion, b.*
from @bitacoraprocesousuario b
inner join @Turno t
on convert(datetime, convert(varchar(12), b.starttime, 114))
order by b.Id
Hi DaleJ,
I do not like much doing that kind of comparison. I would prefer to compare datetime values and not strings.
...
on b.starttime between dateadd(day, datediff(day, t.HoraInicio, b.starttime), t.HoraInicio)
and dateadd(day, datediff(day, t.HoraFin, b.starttime), t.HoraFin)
AMB
Hi DaleJ,
You are right. You are taking just time part and converting it to datetime, so SS will use 19000101 as base date.
Sorry for the confusion,
AMB