Format GETDATE to midnight of current date?

[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

 

[SFAxess]

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.

 

[Paul]

That does what I want. Thank you very much!

Paul

 

[Uwe Ricken]

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

 

[SFAxess]

Excellent substitute!
Thanks for the tip