Format GETDATE to midnight of current date?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 27 Jan 2004 17:49 GMT

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

Reply to this Message

SFAxess - 27 Jan 2004 22:29 GMT

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.

>-----Original Message-----
>I need a way to write a view that will select all the records posted since
[quoted text clipped - 13 lines]
>
>.

Reply to this Message

Paul - 27 Jan 2004 23:44 GMT

That does what I want. Thank you very much!

Paul

> This will do what you need:
> SELECT *
[quoted text clipped - 29 lines]
> >
> >.

Reply to this Message

Uwe Ricken - 28 Jan 2004 06:41 GMT

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 ;-)

Signature

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

Reply to this Message

SFAxess - 28 Jan 2004 17:31 GMT

Excellent substitute!
Thanks for the tip

>-----Original Message-----
>Hi SFAxess,
[quoted text clipped - 10 lines]
>
>HTH ;-)

Reply to this Message