SQL Server - How to filter current date from different dates

Asked By prathap chowdary on 13-Feb-12 05:03 AM
Hi,

This is prathap, i need how to fileter current date only .
for example i am having the different dates like:
2012-02-13 15:29:59.747
2012-02-13 15:28:51.990
2012-02-13 15:29:49.770
2012-02-12 21:37:43.647
2012-02-13 15:30:38.357
2012-02-13 15:30:35.000
2012-02-13 15:30:35.010
2012-02-13 15:29:38.557
2012-02-13 14:08:15.463
2012-02-13 15:29:21.503
2012-02-13 15:27:00.353
2012-02-13 15:30:35.007
2012-02-13 15:30:41.060

from above i want only today date only.
kalpana aparnathi replied to prathap chowdary on 13-Feb-12 05:08 AM
use like this:

WHERE datecol = DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
or
WHERE datecol >= DATEADD(day,DATEDIFF(day,0,GETDATE()),0) AND datecol < DATEADD(day,DATEDIFF(day,0,GETDATE()),1)
Dilip Sharma replied to prathap chowdary on 13-Feb-12 05:10 AM
if u want this in sql server
then 

write this query
select * from tbname
 
where CAST(FLOOR(CAST( EntryDate AS float)) AS datetime) = CAST(FLOOR(CAST( @getdate() AS float)) AS datetime)


Somesh Yadav replied to prathap chowdary on 13-Feb-12 05:11 AM

Try this ,

WHERE signupOpen >= CURdate() AND signupClose <= CURdate()
Ali Raza Rizvi replied to prathap chowdary on 13-Feb-12 05:13 AM
Select  * from tblName 
where Convert(varchar,colname,107)=Convert(varchar,getdate(),107)


this will get only today's date records
Reena Jain replied to prathap chowdary on 13-Feb-12 05:16 AM

Hi,

You can compare date in sql server in diffrend way but u make sure u did not able to compare directly with gatedate().

u need to do little bit extra for comparision as follows

u can compare greader or less than  with dateadd function as

select*from patient where modifieddate > dateadd("d",-1, getdate())

You need to either convert the dates that you are comparing (i.e server date and the database date field) to a common format like mm/dd/yyyy) and then do a comparison.

You have almost done that with the current date, but the calendar date still has the time part and the equal to comparisons wont work.  But using this is dangerous, as the database server and the webserver can exist in different time zones and could mean improper comparison.
use the 112 format.

Select * from  BTtblBannerDisplay where BannerId=" + RequestID + " and CONVERT(CHAR(8),CalendarDate,112)='" + CONVERT(CHAR(8),GetDate(),112)+ "'
Sandeep Mittal replied to prathap chowdary on 13-Feb-12 06:16 AM
There are vaious ways for it
1 of the Example  is
CAST(CONVERT(VARCHAR, DATECOLNAME, 101) AS DATETIME) >= CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) //
,but i would not suggest you to use like this 
Reason : If you would have any index on "DATECOLNAME" and you would modify it to match the value
like this, then index would not be utilized

I would suggest you to use the condition like below.
DATECOLNAME >= CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)
AND
DATECOLNAME < DATEADD(d,1,CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME))
Sandeep Mittal replied to Sandeep Mittal on 13-Feb-12 06:19 AM
There are vaious ways for it. One of the Example  is
CAST(CONVERT(VARCHAR,DATECOLNAME,101) AS DATETIME) = CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME) //
,but i would not suggest you to use like this 
Reason : If you would have any index on "DATECOLNAME" and you would modify it to match the value
like this, then index would not be utilized

I would suggest you to use the condition like below.
Benefits :
1. Index would be utilized if exists on the column
2. No need to perform operation on table field column
DATECOLNAME >= CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)
AND
DATECOLNAME < DATEADD(d,1,CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME))
prathap chowdary replied to kalpana aparnathi on 14-Feb-12 12:28 AM
Thanks .....it is working.....