Solved

sql and msaccess query

Posted on 2011-02-28
10
217 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 84
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

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

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 40

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 40

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

765 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