David McAfee
davidmcafee at gmail.com
Mon Jul 19 12:58:22 CDT 2010
I'd have the query or stored procedure use the function when you are returning data to the FE. But if that's not what you're looking for, here's a function (at the bottom of the page) that will round to seconds in VBA, you might be able to modify it to round to the eharest hour. http://www.everythingaccess.com/tutorials.asp?ID=Rounding-in-Access HTH David On Mon, Jul 19, 2010 at 10:43 AM, Kaup, Chester <Chester_Kaup at kindermorgan.com> wrote: > Thanks for the code however I guess I did not explain what I needed well. This looks like SQL Server code. What I need is VBA code. Thanks. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee > Sent: Monday, July 19, 2010 12:21 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] SQL Server Date Time > > CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) > RETURNS DATETIME AS > BEGIN > DECLARE @RoundedTime smalldatetime > DECLARE @Multiplier float > > SET @Multiplier= 24.0/@RoundTo > > SET @RoundedTime= ROUND(CAST(CAST(CONVERT(VARCHAR, at Time,114) AS > DATETIME) AS FLOAT) * @Multiplier,0)/@Multiplier > RETURN at RoundedTime > END > > > --To use the function: > --SELECT dbo.roundtime(GETDATE(),1) '1=rounds to 1 hour, .5 rounds to > nearest half hour ... > > On Mon, Jul 19, 2010 at 9:24 AM, Kaup, Chester > <Chester_Kaup at kindermorgan.com> wrote: >> I have a database linked to an SQL server table that has a date time column. I want only the date and the hour portion rounded to the nearest hour. For example >> >> 7/18/2010 1:41:00 AM becomes 7/18/2010 2:00:00 AM >> >> Thanks >> >> Chester Kaup >> Engineering Technician >> Kinder Morgan CO2 Company, LLP >> Office (432) 688-3797 >> FAX (432) 688-3799 >> >> >> No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced. >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >