Where with a date and between - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Where with a date and between

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2006-04-10 : 18:39:44

Hi I have a Table tha save in a datetime field the date and time. I need to do clause that do a between.

For example if I do the next query I don't obtain information:

select * from rh_trab

where fecha_ing between '2006-01-17' and '2006-01-17'
What I need to changes?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-10 : 18:48:44

Your query is searching for any data that is between midnight on the 17th and midnight on the 17th. So you'll only get rows back that are exactly midnight. If you want all rows for January, 17 2006, then:

where fecha_ing >='2006-01-17' and fecha_ing < '2006-01-18'

Tara Kizer
aka tduggan

shifis
Posting Yak Master

157 Posts

Posted - 2006-04-10 : 19:06:18

But in case that I need a between clause with '2006-01-01' and '2006-01-18' this dates ( for example)is there any way to conver the fecha_ing to obtain only the date part, I mean what is the best way to do that when you have this situation and the '2006-01-01' and '2006-01-18' will be two var datetime in a SP?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-10 : 19:07:40

You don't need to use a between clause.

where fecha_ing >= @var1 and fecha_ing < DATEADD(d, 1, @var2)

Tara Kizer
aka tduggan

shifis
Posting Yak Master

157 Posts

Posted - 2006-04-10 : 20:28:32

Thanks Tara

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 01:16:49

Also read this
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail