We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?
Run the following and post the results:
select
dateofbusiness
, count (*)
from
MyTable
group by
dateofbusiness
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104271245.345995.62690@.f14g2000cwb.googlegro ups.com...
We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?
|||Try,
where ISDATE(dateofbusiness) <> 0
Message posted via http://www.sqlmonster.com
|||2003-03-25 00:00:00.00015
2003-03-26 00:00:00.00017
2003-03-27 00:00:00.00073
2003-03-28 00:00:00.0009
2003-04-11 00:00:00.00018
2003-04-14 00:00:00.000131
2003-04-15 00:00:00.00011
2003-04-25 00:00:00.0002
2003-04-28 00:00:00.00018
2003-05-06 00:00:00.00017
2003-05-08 00:00:00.00016
2003-05-12 00:00:00.00023
2003-05-13 00:00:00.00080
2003-05-16 00:00:00.00027
2003-05-19 00:00:00.00015
2003-06-05 00:00:00.00023
2003-06-16 00:00:00.00011942
2003-06-17 00:00:00.00012038
2003-06-18 00:00:00.00012166
2003-06-19 00:00:00.00012307
2003-06-20 00:00:00.00012502
2003-06-21 00:00:00.00011840
2003-06-22 00:00:00.00011341
2003-06-23 00:00:00.00011784
2003-06-24 00:00:00.00012055
2003-06-25 00:00:00.00011905
2003-06-26 00:00:00.00012088
2003-06-27 00:00:00.00012451
2003-06-28 00:00:00.00011956
2003-06-29 00:00:00.00011303
2003-06-30 00:00:00.00012072
2003-07-01 00:00:00.00011545
2003-07-02 00:00:00.00011604
2003-07-03 00:00:00.00011857
2003-07-04 00:00:00.000636
2003-07-05 00:00:00.00011045
2003-07-06 00:00:00.00010303
2003-07-07 00:00:00.00011324
2003-07-08 00:00:00.00011002
2003-07-09 00:00:00.00011446
2003-07-10 00:00:00.00011486
2003-07-11 00:00:00.00012115
2003-07-12 00:00:00.00011240
2003-07-13 00:00:00.00010871
2003-07-14 00:00:00.00011395
2003-07-15 00:00:00.00011563
2003-07-16 00:00:00.00019
2003-08-27 00:00:00.00026
2003-08-28 00:00:00.00025
2003-09-16 00:00:00.000805
2003-09-17 00:00:00.000806
2003-09-18 00:00:00.000783
2003-09-19 00:00:00.000365
2003-09-20 00:00:00.000312
2003-09-21 00:00:00.000318
2003-09-22 00:00:00.000334
2003-09-23 00:00:00.000347
2003-09-24 00:00:00.000358
2003-09-25 00:00:00.000390
2003-09-26 00:00:00.000388
2003-09-27 00:00:00.000354
2003-09-28 00:00:00.000340
2003-09-29 00:00:00.000334
2003-09-30 00:00:00.000367
2003-10-02 00:00:00.0002
2003-10-03 00:00:00.0007
2003-10-06 00:00:00.00019
2003-10-08 00:00:00.00013
2003-10-22 00:00:00.00015
2003-12-01 00:00:00.00082068
2003-12-02 00:00:00.00083850
2003-12-03 00:00:00.00084996
2003-12-04 00:00:00.00085926
2003-12-05 00:00:00.00089212
2003-12-06 00:00:00.00086304
2003-12-07 00:00:00.00075057
2003-12-08 00:00:00.00083997
2003-12-09 00:00:00.00083979
2003-12-10 00:00:00.00086040
2003-12-11 00:00:00.00087731
2003-12-12 00:00:00.00091075
2003-12-13 00:00:00.00088179
2003-12-14 00:00:00.00073306
2003-12-15 00:00:00.000161373
2003-12-16 00:00:00.00094041
2003-12-17 00:00:00.00097228
2003-12-18 00:00:00.00099609
2003-12-19 00:00:00.000102561
2003-12-20 00:00:00.00098621
2003-12-21 00:00:00.00085470
2003-12-22 00:00:00.000102115
2003-12-23 00:00:00.000103167
2003-12-24 00:00:00.00050463
2003-12-26 00:00:00.00095627
2003-12-27 00:00:00.00093059
2003-12-28 00:00:00.00087425
2003-12-29 00:00:00.00096371
2003-12-30 00:00:00.00097959
2003-12-31 00:00:00.00081578
2004-02-01 00:00:00.000384
2004-02-06 00:00:00.00026
2004-02-12 00:00:00.00052
2004-02-17 00:00:00.00022
2004-02-19 00:00:00.00017
2004-02-20 00:00:00.00050
2004-02-21 00:00:00.000107
2004-02-23 00:00:00.00090
2004-02-24 00:00:00.00022
2004-02-27 00:00:00.00056
2004-03-01 00:00:00.00056
2004-03-02 00:00:00.00022
2004-03-05 00:00:00.000107
2004-03-08 00:00:00.00022
2004-03-11 00:00:00.000501
2004-04-22 00:00:00.00012
2004-04-29 00:00:00.00022
2004-04-30 00:00:00.00048
2004-05-03 00:00:00.00044
2004-05-04 00:00:00.00019
2004-05-07 00:00:00.00022
2004-05-10 00:00:00.00022
2004-05-13 00:00:00.0001998
2004-05-26 00:00:00.00015
2004-05-28 00:00:00.0005
2004-06-01 00:00:00.00052
2004-06-02 00:00:00.00044
2004-06-03 00:00:00.00031
2004-06-04 00:00:00.00018
2004-06-07 00:00:00.00018
2004-06-16 00:00:00.000672
2004-06-17 00:00:00.000698
2004-06-19 00:00:00.0002
2004-06-20 00:00:00.000640
2004-06-21 00:00:00.000692
2004-06-22 00:00:00.000326
2004-06-23 00:00:00.000378
2004-06-24 00:00:00.000400
2004-06-25 00:00:00.000348
2004-06-26 00:00:00.000345
2004-06-29 00:00:00.00025
2004-06-30 00:00:00.00082
2004-07-01 00:00:00.00023
2004-07-08 00:00:00.00021
2004-07-12 00:00:00.00021
2004-07-15 00:00:00.000467
2004-07-19 00:00:00.000432
2004-07-23 00:00:00.000427
2004-07-27 00:00:00.00021
2004-08-08 00:00:00.000392
|||Also try,
"where convert(char(10),dateofbusiness,120) is not null"
and post results,
Jon
Message posted via http://www.sqlmonster.com
|||OK, so there are none that are not null. Could you please post the DDL for
your table and the exact query you ran?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104272808.874749.162530@.z14g2000cwz.googlegr oups.com...
2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392
|||John we have already done this as a hotfix to our database,
"rtrim(dateofbusiness) is not null" works, too. However we have over
2000 databases that do not have the hot fix, and other code affecting
the table.
SQL is not doing what its supposed to be doing and I'm looking for an
answer. I don't usually post until....
|||Ken,
I did run into this problem in the past and am trying to remember what caused it to happen and how I fixed it. I've tried to replicate the date-time like your example and my query worked just fine.
I thought it had something to do with the hh:mm:ss being all zeros, but I'm not sure that's the problem. I also tried the ansi-null option on the db and it still worked. I also tried the connection object and thought maybe one of the settings was incorr
ectly set, but that did reproduce your error.
If I find out, I'll post or you can email me at: corncrowe@.aol.com
Jon
Message posted via http://www.sqlmonster.com
|||CREATE TABLE [dbo].[Temp_HstSalesByInterval] (
[DateOfBusiness] [datetime] NULL ,
[FKStoreId] [int] NULL ,
[FKRevenueId] [int] NULL ,
[Period] [int] NULL ,
[Type] [int] NULL ,
[TypeId] [int] NULL ,
[TypeId2] [int] NULL ,
[Amount] [float] NULL ,
[OpenHour] [int] NULL ,
[lCount] [int] NULL ,
[DestinationServer] [int] NULL
) GO
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
GO
select * from Temp_HstSalesByInterval where fkstoreid = 23 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 23 will return
results and dateofbusiness is populated.
|||Ken,
The all zeros in hh:mm:ss is most likely the problem. I asked the other programmer here and we both agreed that we encountered this problem when trying to query a date field with zeros in the hh:mm:ss stamp.
Why or how did you manage to write all zeros on a date/time field? In SQL there is no such thing as 00:00:00?
Sorry,
Jon
Message posted via http://www.sqlmonster.com
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment