-
Feb 9th, 2012, 05:31 PM
#1
Thread Starter
Lively Member
[RESOLVED] SQL date and time range
Hi, I have an Access 2007 db, using the following vb.net2008 relevant items:
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim Command As OleDb.OleDbCommand
table1 data:
ID TRACE DATE TRACE TIME TRACE 1 TRACE 2 TRACE 3
1 25/11/2011 00:00:00 2000 1900 2100
2 25/11/2011 01:00:00 2100 2000 2200
3 25/11/2011 02:00:00 2000 1800 2100
4 25/11/2011 03:00:00 1900 1700 2050
5 25/11/2011 04:00:00 1800 1900 1900
6 25/11/2011 05:00:00 1600 1700 1500
7 25/11/2011 06:00:00 1400 1300 1350
8 25/11/2011 07:00:00 1100 1250 1200
9 25/11/2011 08:00:00 800 900 950
10 25/11/2011 09:00:00 400 550 500
11 25/11/2011 10:00:00 200 250 350
12 25/11/2011 11:00:00 0 100 0
13 25/11/2011 12:00:00 0 0 50
14 25/11/2011 13:00:00 0 50 0
15 25/11/2011 14:00:00 150 250 200
16 25/11/2011 15:00:00 350 350 400
17 25/11/2011 16:00:00 650 650 700
18 25/11/2011 17:00:00 650 750 650
19 25/11/2011 18:00:00 650 650 750
20 25/11/2011 19:00:00 850 950 850
21 25/11/2011 20:00:00 850 1000 900
22 25/11/2011 21:00:00 1200 1300 1100
23 25/11/2011 22:00:00 1500 1450 1450
32 25/11/2011 23:00:00 1450 1350 1200
33 26/11/2011 00:00:00 2000 1900 2100
34 26/11/2011 01:00:00 2100 2000 2200
35 26/11/2011 02:00:00 2000 1800 2100
36 26/11/2011 03:00:00 1900 1700 2050
37 26/11/2011 04:00:00 1800 1900 1900
38 26/11/2011 05:00:00 1600 1700 1500
39 26/11/2011 06:00:00 1400 1300 1350
40 26/11/2011 07:00:00 1100 1250 1200
41 26/11/2011 08:00:00 800 900 950
42 26/11/2011 09:00:00 400 550 500
43 26/11/2011 10:00:00 200 250 350
44 26/11/2011 11:00:00 0 100 0
45 26/11/2011 12:00:00 0 0 50
46 26/11/2011 13:00:00 0 50 0
47 26/11/2011 14:00:00 150 250 200
48 26/11/2011 15:00:00 350 350 400
49 26/11/2011 16:00:00 650 650 700
50 26/11/2011 17:00:00 650 750 650
51 26/11/2011 18:00:00 650 650 750
52 26/11/2011 19:00:00 850 950 850
53 26/11/2011 20:00:00 850 1000 900
54 26/11/2011 21:00:00 1200 1300 1100
55 26/11/2011 22:00:00 1500 1450 1450
56 26/11/2011 23:00:00 1450 1350 1200
I'm trying to filter out data from specific times on specific dates, for instance, i'd like to display data:
beginning on 25/11/2011 at 23:00:00
until 26/11/2011 at 02:00:00.
My sql statement is as follows:
SELECT * FROM table1
WHERE
[TRACE DATE] = #11/25/2011# AND [TRACE TIME] = '23:00:00'
BETWEEN
[TRACE DATE] = #11/26/2011# AND [TRACE TIME] = '02:00:00'"
But this is the data I get back, notice the omission of the whole days data for 26/11/2011 and the 23:00:00 entry on the 25/11/2011:
25/11/2011 00:00:00 200019002100
25/11/2011 01:00:00 210020002200
25/11/2011 02:00:00 200018002100
25/11/2011 03:00:00 190017002050
25/11/2011 04:00:00 180019001900
25/11/2011 05:00:00 160017001500
25/11/2011 06:00:00 140013001350
25/11/2011 07:00:00 110012501200
25/11/2011 08:00:00 800900950
25/11/2011 09:00:00 400550500
25/11/2011 10:00:00 200250350
25/11/2011 11:00:00 01000
25/11/2011 12:00:00 0050
25/11/2011 13:00:00 0500
25/11/2011 14:00:00 150250200
25/11/2011 15:00:00 350350400
25/11/2011 16:00:00 650650700
25/11/2011 17:00:00 650750650
25/11/2011 18:00:00 650650750
25/11/2011 19:00:00 850950850
25/11/2011 20:00:00 8501000900
25/11/2011 21:00:00 120013001100
25/11/2011 22:00:00 150014501450
It's almost like it's trying to work backwards? I'm so stumped, i've been working on this for a week now!
-
Feb 9th, 2012, 06:11 PM
#2
New Member
Re: SQL date and time range
Maybe if you do something like:
Code:
SELECT * FROM table1
WHERE ( [TRACE DATE] + [TRACE TIME] ) BETWEEN #11/25/2011 23:00:00# AND #11/26/2011 02:00:00#
-
Feb 10th, 2012, 04:34 AM
#3
Thread Starter
Lively Member
Re: SQL date and time range
Hi I tried that, but it returned all the data for 25/11/2011?
Thanks,
-
Feb 10th, 2012, 07:52 AM
#4
Re: SQL date and time range
sigh... what are the data types for each field? specifically the Trace Date and Trace Time fields... and more importantly, WHY isn't that being stored as a SINGLE datetime field?
-tg
-
Feb 10th, 2012, 08:26 AM
#5
Thread Starter
Lively Member
Re: SQL date and time range
Thanks, yes, i checked the datatypes, and it was set to Text for the time field. Thanks for all the help!
-
Feb 10th, 2012, 08:54 AM
#6
Re: [RESOLVED] SQL date and time range
So WHAT did you do? Does this mean it works now? Take a penny, give a penny... help the next fellow... can you post what you did to fix your problem?
-tg
-
Feb 10th, 2012, 03:04 PM
#7
Thread Starter
Lively Member
Re: [RESOLVED] SQL date and time range
No problems, what I did was change the field data-type in MS Access. In Acess 2007, by right-clicking on the table, brings up an option 'Design View'.
From here, you can select the data-type for each field. It seems that if the data in the field is only going to be one of these data-types, then the SQL statement will only work if that's how it's assigned. I selected 'Date/Time' for my [TRACE DATE] and [TRACE TIME] fields, the others I set to 'Number'.
I'm no expert, but it seems to make sense with the previous post like 'techgnome' pointed out - thanks for that pointer
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|