How to get in Between records from a table

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

How to get in Between records from a table

Author

Topic

Ehtesham Siddiqui
Starting Member

10 Posts

Posted - 2011-09-19 : 10:05:10

Hi,
I have a database table,I have two cloumns first is FromDate second is ToDate my requirement is i should be able to compare the records from these two columns with the current date.
Example.
SrNo FromDate ToDate
1 9/09/2011 28/09/2011
I want to compare that whether my current date is in between FromDate and ToDate

Please Suggest.

Ehtesham Siddiqui
Software Engineer
India

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 10:17:44

Hello,

How about something like the following filter;

WHERE GETDATE() >= FromDate AND GETDATE() <= ToDate

HTH.

Ehtesham Siddiqui
Starting Member

10 Posts

Posted - 2011-09-19 : 10:43:31

Thnks mate its doing well.Another thing that i need is.My table also has a time column.It should also compare the current time of the system with the time in the column and retreive only those records which are between FromDate and Todate and =currenttime
Im trying somethng like this but not working right as needed.
Example
SrNo FromDate ToDate Time
1 9/09/2011 28/09/2011 01/01/1900 9.00 AM
select * from TableName WHERE GETDATE() >= FromDate AND GETDATE() <= ToDate and Time=CONVERT(VARCHAR(30), GETDATE(), 114)
Can u help further

Ehtesham Siddiqui
Software Engineer
India

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 11:23:22

quote:
Originally posted by Ehtesham Siddiqui

Thnks mate its doing well.Another thing that i need is.My table also has a time column.It should also compare the current time of the system with the time in the column and retreive only those records which are between FromDate and Todate and =currenttime
Im trying somethng like this but not working right as needed.
Example
SrNo FromDate ToDate Time
1 9/09/2011 28/09/2011 01/01/1900 9.00 AM
select * from TableName WHERE GETDATE() >= FromDate AND GETDATE() <= ToDate and Time=CONVERT(VARCHAR(30), GETDATE(), 114)
Can u help further

Ehtesham Siddiqui
Software Engineer
India

Hello,

Can you elaborate on the datatypes of these fields (FromDate, ToDate, Time)?

TIA.

Ehtesham Siddiqui
Starting Member

10 Posts

Posted - 2011-09-20 : 01:47:25

Hi,
thanks for the reply.
Three columns in my table ie.FromDate,ToDate,Time are having DateTime Datatype

Ehtesham Siddiqui
Software Engineer
India

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 02:48:12

Ehtesham,

I'm a bit confused. If the time portion has to be equal to the time portion of "now", how are you going to see things on other days than "today" except in the possibly rare occasion that you have an exact time match on another day for an event as today. Though it's possible, I'm not really following the logic.

Using a another example, say you have the following:

[CODE]
declare @getdate datetime
,@fromdate datetime
,@todate datetime

set @getdate='2011-09-20 00:39:19.497' --//simulate getdate timestamp at a specific point in time
set @fromdate = '2011-09-19 00:00:00.000'
set @todate= '2011-09-20 10:00:01.111'

declare @A table (checkdate datetime)

insert into @A (checkdate)
values ('2011-09-18 12:38:55.000')

select checkdate,
CONVERT(time, checkdate,114) as 'TimePortionOfCheckedDate',
CONVERT(time, @getdate,114) as 'TimePortionOfGetDate', --//What is the value of this time?
case when checkdate>=@fromdate and checkdate<@todate and CONVERT(time, checkdate,114)=CONVERT(VARCHAR(30), @getdate, 114) then 'pass' else 'fail' end

from @A
[/CODE]

It seems to be that unless you want to know all events between a from/todate that happened at exactly the same time, the time filter is a bit unusual.

Ehtesham Siddiqui
Starting Member

10 Posts

Posted - 2011-09-20 : 04:06:30

Thanks mate for ur suggestions..
But the thing is the records will be checked each second.First the comparison will be made weather the current date exists in between fromdate and ToDate and if yes then it should check for the time.Its kinda application working in the background with no UI.

Ehtesham Siddiqui
Software Engineer
India

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources