Gerardo Cortes


Hi, i have this problem :

I have to compare 1 hour between two days , example:

I have this datetime value (BitacoraProcesoUsuario.FechaInicioProceso) in 108 format : 23:35:22

and I make this query:

Select TOP 100 * from BitacoraProcesoUsuario
INNER JOIN Turno
ON convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) > = '24:00:00'
AND convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) <= '06:29:00'

and returns nothing........ this because 23:35:22 is gtreater than 06:29:00' value, my question is how can I compare this hours in this format.

Regards



Re: comparing hours

DaleJ


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







Re: comparing hours

hunchback

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






Re: comparing hours

Gerardo Cortes

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 BitacoraProcesoUsuario

INNER JOIN Turno

ON Convert(varchar, BitacoraProcesoUsuario.FechaInicioProceso, 108) >= '22:00:00' AND

Convert(varchar, BitacoraProcesoUsuario.FechaFinProceso, 108) <= dateadd(day, 1, '06:30:00')





Re: comparing hours

hunchback

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





Re: comparing hours

Gerardo Cortes

Hi hunchback,

Actually , I dont have any row less than '1900-01-02 06:30:00.000, because we take only the hour, not the day.

Regards





Re: comparing hours

DaleJ

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.

Code Block

Select TOP 100 * from BitacoraProcesoUsuario

INNER JOIN Turno

ON datepart(hh, BitacoraProcesoUsuario.FechaInicioProceso) < 6

OR (datepart(hh, BitacoraProcesoUsuario.FechaInicioProceso) = 6

AND datepart(mi, BitacoraProcesoUsuario.FechaInicioProceso) < 30)






Re: comparing hours

Gerardo Cortes

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" .





Re: comparing hours

DaleJ

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.






Re: comparing hours

Gerardo Cortes

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





Re: comparing hours

hunchback

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





Re: comparing hours

DaleJ

Gerardo,

Here is a stab at what you might be trying to accomplish.

I split the Nocturno entry in the Turno table into two entries, and used the system default date of 01/01/1900.
I then made an assumption that the fields in the @bitacora... table were also datetime fields.
I then extract the time from those fields and combine them with the default date to get the comparator.



Code Block



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))

between t.HoraInicio and t.HoraFin

order by b.Id







Re: comparing hours

hunchback

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





Re: comparing hours

DaleJ

Thanks Alejandro!

My code does do a compare on dates, I strip the time from Horalnicio and use it to create a work date of 01/01/1900, then compare that to the range in the lookup table.

But I see your point on the datemath.

Much appreciated!






Re: comparing hours

hunchback

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