Solved

sql and msaccess query

Posted on 2011-02-28
10
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 500 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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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