Click here to Skip to main content
15,846,346 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
It is a VB6 project connected with Ms access database(.accdb).
The table has separate columns for DATE and TIME.
Below is the table

TABLE : tblSales

+------------------------------------------- ---+
|  ID	|   PRODUCT    |    DATE     |   TIME   |
+-------|--------------|-------------|----------+
|   1	|    SAND      | 11-Nov-2022 | 13:50:33 |
|   2	|    CEMENT    | 11-Nov-2022 | 18:22:05 |
|   3	|    BRICKS    | 12-Nov-2022 | 11:09:12 |
|   4	|    CEMENT    | 13-Nov-2022 | 09:50:42 |
|   5	|    SAND      | 13-Nov-2022 | 14:47:48 |
|   6	|    SAND      | 13-Nov-2022 | 20:22:31 |
|   7	|    BRICKS    | 15-Nov-2022 | 12:17:26 |
|   8	|    BRICKS    | 16-Nov-2022 | 08:36:27 |
|   9	|    SAND      | 16-Nov-2022 | 15:50:44 |
|  10	|    CEMENT    | 20-Nov-2022 | 20:50:18 |
+-------|--------------|-------------|----------+


What I have tried:

SQL
Select * From tblSales Where [DATE] Between from_date And to_date And [TIME] Between from_time And to_time


Results were Wrong.

Need advise for getting expected result. Thanks.
Posted
Updated 14-Nov-22 20:55pm
Comments
Richard MacCutchan 15-Nov-22 4:01am    
"Results were Wrong."
Well we cannot see your screen so have no way of knowing why that might be. You also need to show what values you are using in the comparison fields.
PIEBALDconsult 15-Nov-22 6:56am    
Maybe something along the lines of ... WHERE [DATE] + [TIME] BETWEEN ... as appropriate for the database.

1 solution

If you are storing dates and times separately, then the most likely thing is that your are storing them as VARCHAR or NVARCHAR fields, and that's a huge mistake: use a single DATETIME field instead, and a lot of problems will go away.

Text based dates and times are a very bad idea: not only is it pretty easy to wreck your db by storing invalid data in them (like "I dunno" for example) it's also very easy to get the format inconsistent: 01-02-03 can be three different dates depending on the user that entered the: 1st Feb 2003, 2nd Jan 2003, and 3rd Feb 2001, for European, American, and Japanese / ISO users respectively.

And text based comparisons are evaluated by finding the first different character pair and returning the result of that comparison without even looking at subsequent characters in either string you are comparing. So even if your database somehow only contains data of the form dd-MMM-yyyy it still doesn't compare well, as "13-Dec-2022" is before "13-Nov-2022" because 'D' comes before 'N' in the alphabet!

Change your DB, use appropriate datatypes and all these problems disappear.
 
Share this answer
 
Comments
Member 11291660 15-Nov-22 2:05am    
@OriginalGriff , for a reason we have separate columns for Date and Time. Moreover , the datatype of those columns are of type DATETIME, not VARCHAR or NVARCHAR.
OriginalGriff 15-Nov-22 2:36am    
Then you need to explain what it does that you didn't expect, or not do that you did.
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?

Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with: so we have no idea what you get, or what your expected result is!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900