Results 1 to 7 of 7

Thread: [RESOLVED] SQL date and time range

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    Guernsey, UK
    Posts
    104

    Resolved [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!

  2. #2
    New Member
    Join Date
    Feb 2012
    Location
    3rd rock from the sun
    Posts
    1

    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#

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    Guernsey, UK
    Posts
    104

    Re: SQL date and time range

    Hi I tried that, but it returned all the data for 25/11/2011?

    Thanks,

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    Guernsey, UK
    Posts
    104

    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!

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    Guernsey, UK
    Posts
    104

    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
  •  



Click Here to Expand Forum to Full Width