Solved

sql and msaccess query

Posted on 2011-02-28
10
216 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now