Format GETDATE to midnight of current date?

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I need a way to write a view that will select all the records posted since
midnight of the current date. The table uses a datetime field that contains
the exact time a record was added. The best I've been able to do is to pull
the last 24 hours by using;

SELECT *
FROM visits
WHERE (visitdate > CONVERT(DATETIME, GETDATE() - 1, 103))

I need to only view records since midnight if at all possible. Can you help
me adjust the query?

Thanks.
Paul
 
This will do what you need:
SELECT *
FROM visits
WHERE visitdate>=CAST(CAST(MONTH(getdate())
as varchar) + '-' + Cast(DAY(GetDate())
as varchar)+ '-' + Cast(YEAR(GetDate())
as varchar)as DateTime)

Note: using the >= will include a record where the time
is set to 00:00:00, which is common if you use the VBA
Date() function a lot in Access.
 
Hi SFAxess,
hi Paul

that query seems to not so perfomant because of multiple
using of functions

Better way ?!?

SELECT * FROM dbo.visists
WHERE CONVERT(varchar(10), visitdate, 112) =
CONVERT(varchar(10), getdate(), 112)

HTH ;-)

--
Gruß, Uwe Ricken
MCP for SQL Server 2000 Database Implementation

GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
____________________________________________________
APP: http://www.AccessProfiPool.de
dbdev: http://www.dbdev.org
FAQ: http://www.donkarl.com/AccessFAQ.htm
 
Back
Top