StartDate and EndDate

[strRusty_gal]

HiEveryOne,

Does anyone know how to get the data where startdate is more than today'sdate and enddate is less than today'sdate and if enddate is NULL.

Thanks for the help

[urtrivedi]

startdate is more than today'sdate and enddate is less than today'sdate

Above sentence is unlikely to happen, any way query is as follows
1

select * from tablename where current_date<start_date and enddate<current_date

2 if end date is null

select * from tablename where current_date<start_date and enddate is null

[debasisdas]

Let me understand for which date you want to fetch the data.

[sknake]

Its very difficult to understand your question. Here's my guess at what you're after:

Declare @StartDate DateTime, @EndDate DateTime
Set @StartDate = CAST(Floor(CAST(GetDate() as float)) as DateTime) --Trim off the time portion
Set @EndDate = @StartDate + 1

Select *
From SomeTable
Where CreateDate >= @StartDate and (CreateDate < @EndDate or CreateDate Is Null)

[adam_k]

Its very difficult to understand your question. Here's my guess at what you're after:

Declare @StartDate DateTime, @EndDate DateTime
Set @StartDate = CAST(Floor(CAST(GetDate() as float)) as DateTime) --Trim off the time portion
Set @EndDate = @StartDate + 1

Select *
From SomeTable
Where CreateDate >= @StartDate and (CreateDate < @EndDate or CreateDate Is Null)

wouldn't it be easier to convert both dates (createdate or enddate and current) to the same format and compare them? why use variables for something like this?

select * from SomeTable
where convert(char(10),CreateDate,101) >= convert(char(10),getdate(),101) and (convert(char(10), EndDate, 101) = convert(char(10),dateadd(d,1,getdate()),101) or EndDate is null )

This way even if EndDate contains time you will get the result without having to add a day (which won't really make much sense when you visit the code after a couple months, without having the data in mind)

[sknake]

wouldn't it be easier to convert both dates (createdate or enddate and current) to the same format and compare them? why use variables for something like this?

select * from SomeTable
where convert(char(10),CreateDate,101) >= convert(char(10),getdate(),101) and (convert(char(10), EndDate, 101) = convert(char(10),dateadd(d,1,getdate()),101) or EndDate is null )

You don't have to use variables... I was using variables for the sake of clarity. Also by searching on a computed value (CONVERT) you won't take advantage of indexes. The SQL Server will have to take every row in the table and convert the date columns with format 101, then compare. If this is a large table it could very well introduce performance problems.

I can't find the MSDN link but its on there somewhere. Here is a blurb from another site:

SELECT c1, dt FROM #dts WHERE CONVERT(char(8), dt, 112) = '20040305'

That returned the expected rows, but there's another problem. Since we do a calculation on the column side (using the CONVERT function), SQL Server cannot use an index to support this search criteria. This can be disastrous for performance! So let's try BETWEEN: