SQL

Jak

Em ok,

SQL ... just started toying with it 2 days ago as the database i'm working with was too big for Excel - (148,000 lines of stuff approx)

Main Problem

---

One of the the three fields is in Date/Time format. I.e 15/05/00 13:20:05

I am dealing with the week 15/05 to 21/05 but for each day I only want to consider times between 12pm and 3pm.

Now obviously i have been able to sort out the days between 15/05 and 21/05. But I can't sort by time only - which is what i need to do. So how given the format would i filter out times =>12pm and <=3pm ?


The secondary problem

---

Once i figure this out, there is a way to automate some kinda macro so it would ask the days to consider then the times and then just do it - Isn't there?


Cheers for any help as usual,

JAK-work (as seen on irc )

kayos

Right then as far as I can see from you post here is some code that will help

Main Problem
SELECT * FROM <Table> WHERE
DATEPART(Hour,<Date> ) BETWEEN 12 AND 2
ORDER BY DATEPART(Hour,<Date> ),DATEPART(Minute,<Date> )

Some kinda macro ?? Do you mean a SP just have four parameters
StartDate
EndDate
StartTime
EndTime

and just work from there. Hope this helps.

Cheers,
Kayos

Kali

if your going to be dealing with time and date seperately like that then put them in seperate fields.
would make your life a lot easier.

Jak

That's exactly what i want to do Kali.

The date/time format is the way the other program (A very specific database manager) exports the data into Access.

So i have the following fields:

Machine#

---

Date/TimeStart

---

Duration
12

---

15/05/00 15:31:05

---

90


What i am trying to get is 4 fields.

Machine Datestart Timestart Finishtime
12 15/05/00 15:31:05 17:01:05


So the first problem is how do i separate Date/Time (what you suggested didn't work unfortunately Kayos). I have tried datepart etc. and also using masks. But these only change the appearance ... If i sort the data by time, it still goes by date first. What i need is two separate columns.


The new second problem is how to convert a number value (e.g 90) into something i can add to the time field to give a finish time.

I've worked out a simple maths theoty which would do this, but coding this would be a royal bit<h ... so i was wondering if the almighty Access already had something for converting it.

Cheers again,

JAK.

[This message has been edited by Jak (edited 08-06-2000).]

Jak

Anyone ...