I am trying to query the number of transactions that occur on a given day with the following query
select COUNT(seq_no) from "table"
where start_time >= '2020-04-01 00:00:00'
and start_time < '2020-04-01 23:59:59'
So I would like a count of the total sequence numbers for April 1st to display. But the results return
What am I doing wrong with this query? SQL Server2000, and I am very new and green to SQL. Thanks for any input.
Are there any transactions on that particular day?
Check the data "by eye" and see if it's the query that's "missing" them or if they really aren't there at all!
Your query looks OK to me.
Your table I'm less convinced about.
Having a table called "table" is a Bad Idea.
Using any reserved word as an identifier (table, column, anything else) is likely to come back and bite you at some point. Save yourself the trouble; you should never need to "protect" the identifiers in your SQL.
What Data Type are you using for the start_time column?
I'm hoping it's a Date-related one but, given the problem you're having, I'm guessing it's not and that the values stored in it are in a different "format".
For example:
select top 5 *
from table1 ;
+--------+-----------------------+
| seq_no | start_time_as_varchar |
+--------+-----------------------+
| 111 | 25-Apr-2021 00:00:01 |
| 222 | 26-Apr-2021 00:00:01 |
| 333 | 27-Apr-2021 00:00:01 |
| 444 | 28/04/2021 00:00:01 |
| 555 | 2021-04-29 00:00:01 |
+--------+-----------------------+
The values are perfectly valid Character Representations of date values, but they're not held in [a column with] a Date Data Type, so SQL Server will do a character-by-character comparison, not a date-wise comparison.
Always store Date Values in Date[-related] Data Types.
...well I'm dumb. I didnt realize I had the year wrong.
I was looking for data from 2021
