Registries in the StartDate<=r<=EndDate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Registries in the StartDate<=r<=EndDate

Author

Topic

chorofonfilo
Starting Member

40 Posts

Posted - 2008-02-06 : 13:26:30

Hello all and thank you very much for your help on this.

I built a query to obtain a certain number of registries that belong to a specified date range:

CREATE procedure giverqbydates
@fi datetime,
@ff datetime
as
Select rq.idreq from requerimientomens rq where rq.fechagenreq between @fi and @ff

GO

When i execute the query on a desired date range i obtain the registries from the start date to the ones right a day before the end date instead of the registries included also in the end date entered.

Eg:
idreq fechagenreq
00001 2008-02-05 12:42:12.953
00002 2008-02-06 11:53:00.377

exec giverqbydates '05/02/2008','06/02/2008' (dd,mm,yy)

I just obtain:
idreq fechagenreq
00001 2008-02-05 12:42:12.953

If i wanna obtain both registries i should put the dates like this:
exec giverqbydates '05/02/2008','07/02/2008' (dd,mm,yy)

And then the result is:
idreq fechagenreq
00001 2008-02-05 12:42:12.953
00002 2008-02-06 11:53:00.377

Could anyone help me about how to make my function work to include the registries that belong to the end date as well so it will eficiently work as: date1<=registries<=date2.

Thank you very much for your help and time.

Perseverance worths it...:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:50:43

CREATE procedure giverqbydates
@fi datetime,
@ff datetime
as
Select rq.idreq from requerimientomens rq where rq.fechagenreq >= @fi and rq.fechagenreq <= @ff

GO

chorofonfilo
Starting Member

40 Posts

Posted - 2008-02-06 : 14:17:10

quote:
Originally posted by visakh16

CREATE procedure giverqbydates
@fi datetime,
@ff datetime
as
Select rq.idreq from requerimientomens rq where rq.fechagenreq >= @fi and rq.fechagenreq <= @ff

GO

I forgot to mention that i tried that before, but still is not working.
I am trying to figure how to make this work.
Thank you visakh16.

Perseverance worths it...:)

chorofonfilo
Starting Member

40 Posts

Posted - 2008-02-06 : 17:56:34

Well basically the field in which the dates were registered is a datetime type field, in which the program saves the dates ussing the actual date PLUS the time in which the saving action was performed so literally the execution of the procedure works like this:

CREATE procedure giverqbydates
@fi datetime,
@ff datetime
as
Select rq.idreq from requerimientomens rq where rq.fechagenreq >= @fi and rq.fechagenreq <= @ff

exec giverqbydates '05/02/2008','06/02/2008' (dd,mm,yy)

gives me all the records from the date 05/02/2008 starting from the 00:00:00 hour to the ones ending at the date 06/02/2008 till the 00:00:00 hour, but NOT considering the records generated on that same end date with a generation hour further than the 00:00:00 hour.

So the result after testing this program was.

00001 2008-02-05 12:42:12.953

In this case we need to build a procedure that works just with them; day,month and year of both dates(starting and ending), so the comparisons will be just based on them, and not on the time attached to the field.

I made this:

create procedure giverqbydates
@fi datetime,
@ff datetime
as
Select rq.idreq from requerimientomens rq
where datepart(dd,rq.fechagenreq)>=datepart(dd,cast(@fi as datetime))
and datepart(dd,rq.fechagenreq)<=datepart(dd,cast(@ff as datetime))
and datepart(mm,rq.fechagenreq)>=datepart(mm,cast(@fi as datetime))
and datepart(mm,rq.fechagenreq)<=datepart(mm,cast(@ff as datetime))
and datepart(yy,rq.fechagenreq)>=datepart(yy,cast(@fi as datetime))
and datepart(yy,rq.fechagenreq)<=datepart(yy,cast(@ff as datetime))

And the result after executing the fixed procedure was this:

exec giverqbydates '05/02/2008','06/02/2008'

00001 2008-02-05 12:42:12.953
00002 2008-02-06 11:53:00.377

Which is what i was looking for.

So in order to avoid all this problems when we have a field that is meant to just store dates, we must use perform the storing action ussing a function that ensures to add the date with the default time form 00:00:00,unfortunately this wasn't the situaton,and a lot of dates with the time attached were stored, thing for i was looking for a workaround that i finally found.

Thank you all for your time and help.

Perseverance worths it...:)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-06 : 18:52:06

You are way over-complicating things -- Just add 1 day to your end date.

where fechangenreq >= @fi and feqchagenreq < @ff+1

That's it.

- Jeff
http://weblogs.sqlteam.com/JeffS

chorofonfilo
Starting Member

40 Posts

Posted - 2008-02-06 : 21:22:45

quote:
Originally posted by jsmith8858

You are way over-complicating things -- Just add 1 day to your end date.

where fechangenreq >= @fi and feqchagenreq < @ff+1

That's it.

- Jeff
http://weblogs.sqlteam.com/JeffS

Exactly Jeff, it took some time for the answer to come up, and it was on the more complicated way, lol, but well i am sure we all improve, lol.

Your workaround works great, and you DON'T need to cast the values(as i thought) if the dates strings are in the correct 'mm/dd/yyyy' or 'dd/mm/yyyy' format.

alter procedure giverqbydates
@fi datetime,
@ff datetime
as
Select rq.idreq from requerimientomens
where rq.fechangenreq>=@fi as datetime and rq.fechangenreq<@ff +1

Thank you very much for your suggestion.

Perseverance worths it...:)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-07 : 07:13:51

quote:
you DON'T need to cast the values(as i thought) if the dates strings are in the correct 'mm/dd/yyyy' or 'dd/mm/yyyy' format.

No...you don't need to cast because those parameters are already declared as datetime types. By definition, they already are and will always be valid dates (though maybe null).

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources