0

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

enter image description here

What am I doing wrong with this query? SQL Server2000, and I am very new and green to SQL. Thanks for any input.

1
  • Tthis is a system I inherited. I am not a SQL/DBA and I am working to migrate away from it. – mcv110 Apr 29 at 16:14
2

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.

1
  • so the table name is not actually table. As for the Data Type of start_time I believe its Datetime value? And that query (select top 5 * from table1 ;) returned values starting in 2020...which has me very concerned that there is no recent data. We only powered on this machine a month ago (VM clone replacing the physical) However I would have expected data more recent as that data was apart of the VM image that was made. – mcv110 Apr 29 at 14:32
1

...well I'm dumb. I didnt realize I had the year wrong.

I was looking for data from 2021

1
  • Thank you for providing the answer to your own question. Please ignore any previous comments you may have seen from reviewers that seemed to be having an off day. Nevertheless, the outcome is a question and answer that is rather localized, so I have closed it. – Paul White Apr 30 at 7:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.