March 28, 2014 at 11:07 am
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.
March 28, 2014 at 11:25 am
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?
March 28, 2014 at 11:35 am
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..
March 28, 2014 at 12:39 pm
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,
March 28, 2014 at 12:52 pm
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)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply