Date Check - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Date Check

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-01-18 : 04:18:34

Hi all,
I have two tables in my table startDate and Enddate. I want to check whether a given date is avalible in between the startDate and
Enddate.How can I. some times the startDate or Enddate may be null. How can I ?

Thanks
Dana

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 04:30:38

Before making any assumption, do the StartDate and EndDate columns contain TIME informatin other than 00:00:00.000 ?

E 12°55'05.25"
N 56°04'39.16"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-01-18 : 04:35:59

The StartDate and EndDate are of DateTime Datatype.. and the value of will be something like
'2007-12-28 00:00:00.000' and '2007-12-31 00:00:00.000'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 04:40:51

where @given_date between Startdate and Enddate

Madhivanan

Failing to plan is Planning to fail

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-01-18 : 04:51:24

Thanks Madhivanan,
I did like this

select * from table where Convert(datetime,'25-12-2007',105) between startDate and EndDate

Now I need one more point to check, if the Enddate is empty then i have to take the current date as Enddate
How to modify the query ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 05:00:54

select * from table where Convert(datetime,'25-12-2007',105) between startDate and coalesce(EndDate,dateadd(day,datediff(day,0,getdate()),0))

Madhivanan

Failing to plan is Planning to fail

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-01-18 : 05:17:10

One point to check here. I am using this qry.

select * from table where Convert(datetime,'25-12-2007',105) between startDate
and coalesce(EndDate,dateadd(day,datediff(day,0,getdate()),0))

It shoud return the Datevalues which are greater than '25-12-2007' and Lesser than Today. But this qry returned some of the values in startDate like

StartDate

2007-07-23 00:00:00.000
2006-08-19 00:00:00.000
2006-09-01 00:00:00.000

What could be problem ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 06:01:32

-- Peso 1
select *
from table1
where (startdate is null and @thewanteddate <= enddate)
or (startdate <= @thewanteddate and enddate is null)
or (startdate <= @thewanteddate and @thewanteddate <= enddate)

-- Peso 2
SELECT *
FROM Table1
WHERE @theWantedDate BETWEEN ISNULL(StartDate, @theWantedDate) AND ISNULL(EndDate, @theWantedDate)

E 12°55'05.25"
N 56°04'39.16"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-01-18 : 06:07:42

Dear Peter,
I tried this one also .. But with the same result

select * from table where Convert(datetime,'25-12-2007',105) between startDate
and IsNUll(EndDate,(Convert(datetime,getdate(),105)))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 07:05:58

Why do you keep insisting on converting the dates?
Are StartDate and EndDate NOT datetime? Are they VARCHAR?

E 12°55'05.25"
N 56°04'39.16"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-01-18 : 07:39:04

No they are DateTime only

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources