?
Solved

sql and msaccess query

Posted on 2011-02-28
10
Medium Priority
?
223 Views
Last Modified: 2012-05-11
i have msaccess query
SELECT locations.storename, datafile1.[star time], datafile1.[end time], datafile1.SumOfin, locations.storeid
FROM datafile1 INNER JOIN locations ON datafile1.vendid = locations.locationid;

i am looking for a result for round of time on end time i mean end time if it shows 11/22/2011 12.45:00 it is should show as 11/22/2011 13:00:00

only for end time

can some help me out with this.

0
Comment
Question by:romeiovasu
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 6

Expert Comment

by:anushahanna
ID: 35001034

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(@Time as float) * @Multiplier,0)/@Multiplier
return @RoundedTime
end
 
SELECT locations.storename, datafile1.[star time], dbo.roundtime(datafile1.[end time],1) , datafile1.SumOfin, locations.storeid
FROM datafile1 INNER JOIN locations ON datafile1.vendid = locations.locationid;

drop [dbo].[RoundTime]

reference: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23341874.html
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 35001039
i meant

drop function [dbo].[RoundTime]
at the end to clean it up.
0
 
LVL 85
ID: 35001204
User Defined Functions are not available in Access, so I'm not sure the earlier comments are relevant.

You can use the Round() function that is built into Access, perhaps:

SELECT locations.storename, datafile1.[star time], Round(datafile1.[end time],1) As EndTime_Rounded, datafile1.SumOfin, locations.storeid
FROM datafile1 INNER JOIN locations ON datafile1.vendid = locations.locationid;

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:romeiovasu
ID: 35001393
both are not working.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35001722
Can you try this?
SELECT locations.storename, 
       datafile1.[star time], 
       DATEADD("h",HOUR(DATEADD("n",30,datafile1.[end time])), 
               FORMAT(datafile1.[end time],"Medium Date")) End_Time, 
       datafile1.SumOfin, 
       locations.storeid 
  FROM datafile1 
       INNER JOIN locations 
         ON datafile1.vendid = locations.locationid;

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35001753
Use AS keyword if you get any error.
SELECT locations.storename, 
       datafile1.[star time], 
       DATEADD("h",HOUR(DATEADD("n",30,datafile1.[end time])), 
               FORMAT(datafile1.[end time],"Medium Date")) AS End_Time, 
       datafile1.SumOfin, 
       locations.storeid 
  FROM datafile1 
       INNER JOIN locations 
         ON datafile1.vendid = locations.locationid;

Open in new window

0
 

Author Comment

by:romeiovasu
ID: 35002348
it works pefectly but only one small thing it if 1.15 it should convert it into 1.30 is it possible. even if 1.12 then 1.30, 1.10 to 1.30
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35002606
You asked to round it to nearest hour. Do you want to round to nearest 30-minutes? If so 1.10 should be 1.00 right?
0
 

Author Comment

by:romeiovasu
ID: 35002805
or else is it possible to make round figure to 1.10 to 2.00
0
 

Author Closing Comment

by:romeiovasu
ID: 35002816
Thanks a lot Sharath i figure it out.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question