Mystery with date range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Mystery with date range

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 05:58:27

Select top 100 *, FirstArchivalDate
From tblCOMStudy with (nolock)
Where FirstArchivalDate between '2012-07-10' and '2012-07-23'
Order by FirstArchivalDate

This only yields records for the 10th July, yet if I change it to the 11th, I'll get records with the archival date of the 11th!

Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-24 : 06:04:26

So the first 100 rows are for 11th july? Try changing the condition yo

Where FirstArchivalDate between >='20120710' FirstArchivalDate <'20120724'

See here for more information
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx

Madhivanan

Failing to plan is Planning to fail

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 06:22:05

Incorrect syntax near '>'.

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 06:26:39

Also tried

Where FirstArchivalDate > '20120710' and FirstArchivalDate <'20120724'
Where FirstArchivalDate between '20120710' and '20120724'

Same results, nothing beyond the 10th (but there are definitely records)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-24 : 06:42:10

What is the datatype of FirstArchivalDate? Also remove top 100 and see if you get all records

Madhivanan

Failing to plan is Planning to fail

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 06:43:02

Here's a sample of the date value...

2012-07-10 10:28:43.683

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-24 : 06:59:40

Read my previous reply

Madhivanan

Failing to plan is Planning to fail

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 07:12:50

Datetime, Null

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 07:15:21

Removing the top 100 did it with "between '20120710' and '20120724' "... Thanks!! )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-24 : 07:18:54

But don't use between, use this

Where FirstArchivalDate between >='20120710' and FirstArchivalDate <'20120724'

Madhivanan

Failing to plan is Planning to fail

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 08:37:12

Will do - but also, is there a way to group them according to day?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-24 : 08:41:58

typo error there

quote:
Originally posted by madhivanan

But don't use between, use this

Where FirstArchivalDate ~~between~~ >='20120710' and FirstArchivalDate <'20120724'

Madhivanan

Failing to plan is Planning to fail

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-24 : 08:48:44

quote:
Originally posted by khtan

typo error there
quote:
Originally posted by madhivanan

But don't use between, use this

Where FirstArchivalDate ~~between~~ >='20120710' and FirstArchivalDate <'20120724'

Madhivanan

Failing to plan is Planning to fail

KH
[spoiler]Time is always against us[/spoiler]

Yes thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources