select based on date compare

Question

Post reply

select based on date compare

Annee

Ten Centuries

Points: 1064
More actions
March 28, 2014 at 11:07 am

#148378

I want to retrieve all the records from a table where the ActiveFromDate(column with datetime datatype) is less than or equal to current date and
ActiveToDate(column with datetime datatype) is greater than equal to current date and I am using the following query:
select * from vendorInfo where (activefromdate <=Convert(datetime, GetDate(), 101)) AND (activetodate >= Convert(datetime, GetDate(), 101))
It is working well except in one scenario when ActiveToDate = currentDate, where it doesn't return true.
The reason is I am saving only date in these fields and I want to compare only the date and not datetime
but this query is taking time as well into consideration.
Can some one please help me modify the query that checks only date and not datetime. Thankyou.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Are you trying to get everything not equal to today in terms of the date?

It's a little confusing what you're saying.

If today is 20140328 and I have this data:

ActiveFromDate

--------------------

20140301 14:45

20140327 12:23

20140328 3:34

20140401 4:45

What do you want returned?

Annee Ten Centuries Points: 1064 More actions · Answer 2

OK.. let me simplify my query:

select * from vendorInfo where activetodate >= Convert(datetime, GetDate(), 101)

I would like to get all the records where activetodate is current date or future date...

But when I use the above query, it is comparing the time as well and not returning today's records.

Just want to compare date and not datetime..

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

Typically when you do this, you want to build a date for today. Keep in mind that 20140328 is 1200am, midnight last night. 11:59pm is the last part of the day.

I'd do something like this:

select @dt = cast( year( getdate()) + month(getdate()) + day(getdate()) as datetime)

Use that in your query,

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

This is a repeated post which was asked in here: http://www.sqlservercentral.com/Forums/Topic1555882-391-1.aspx

I would use the date math as Sean suggested to get

DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2