March 5, 2002 at 3:20 pm
I would like to build a view where records are shown with a where clause on today's date.
I am new to SQL Server and am struggling with date types.
My field is of datetime format.
In MsAccess I'll do it this way:
SELECT tblNoticias.No_Id
FROM tblNoticias
WHERE (((tblNoticias.No_InputFecha)=Date()));
Jean-Luc
Jean-Luc
Jean-Luc
www.corobori.com
March 5, 2002 at 3:38 pm
A few ways. If this is strict sql, I'd do
where datepart( year, mydate) = datepart( year, getdate())
and datepart( month, mydate) = datepart( month, getdate())
and datepart( day, mydate) = datepart( day, getdate())
Steve Jones
March 5, 2002 at 4:18 pm
March 5, 2002 at 4:20 pm
Actually I would go this route with SQL
WHERE CONVERT(VARCHAR,mydate,101) = CONVERT(VARCHAR,GETDATE(),101)
This has IMHO less overhead on the CPU in comparing since both sides are converted and compared in the following format
mm/dd/yyyy = mm/dd/yyyy
And fewer compares are actually done. However again there are litterly dozens of ways.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
December 8, 2002 at 8:22 pm
Since dates are actually stored in the data base a floating point numbers, i think the fastest way to get today's data for comparison would be something like
select *
from table
where cast(datecol as bigint) = cast(getdate() as bigint)
December 9, 2002 at 7:29 am
None of these allow the use of indexes
Use this
WHERE mydate >= CONVERT(VARCHAR,GETDATE(),101)
AND mydate < DATEADD(d,1,CONVERT(VARCHAR,GETDATE(),101))
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply