?
Solved

sql and msaccess query

Posted on 2011-02-28
10
Medium Priority
?
221 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 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