Problem with "select" - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with "select"

Author

Topic

maxxsqlserver
Starting Member

6 Posts

Posted - 2007-11-27 : 04:04:15

I'm Italian cause my english will be not very well.
So, my problem is that if I run this 2 query below, the first run ok and the second doesn't give me the aspected result, I don't know why.

select COUNT(DISTINCT(serial_number)) AS totale from riparazioni_ict
where data >= '26/11/2007'

select COUNT(DISTINCT(serial_number)) AS totale from riparazioni_ict
where (data <= '26/11/2007' and data >= '26/11/2007')

Can you help me? Thank you!

Maxx

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-27 : 04:10:05

Of course the two SELECTs can't give identical results. The second one will return data only for records with date 26/11/2007 whereas first one returns all records with or above date 26/11/2007.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

maxxsqlserver
Starting Member

6 Posts

Posted - 2007-11-27 : 05:18:00

Yes, you're right, but i was wrong explaining the problem.

the second query doesn't return the result expected beacause it returns "totale = 0" even if on 26/11/2007 in my database there are 4 records!
I believe is a problem of the key word 'and' because if i run the first query(where there isn't the condition 'and' it runs ok).
How can I solve the problem?

Maxx

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-27 : 05:23:55

does the date field stored in your database actually have a time stored as well?

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 05:27:53

[code]select COUNT(DISTINCT(serial_number)) AS totale
from riparazioni_ict
where data >= '20071126'
and data < '20071127'[/code]

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

maxxsqlserver
Starting Member

6 Posts

Posted - 2007-11-27 : 05:54:24

quote:
Originally posted by elancaster

does the date field stored in your database actually have a time stored as well?

Em

Yes it does

Maxx

maxxsqlserver
Starting Member

6 Posts

Posted - 2007-11-27 : 05:58:12

quote:
Originally posted by khtan

select COUNT(DISTINCT(serial_number)) AS totale
from riparazioni_ict
where data >= '20071126'
and data < '20071127'

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

Ok it runs ok, but if i call this query from my application code, where the dates are chosen by the users, i have to make a method that set the end date at the day after, is it right?

Maxx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 05:59:59

[code]select COUNT(DISTINCT(serial_number)) AS totale
from riparazioni_ict
where data >= @date
and data < dateadd(day, 1, @date)[/code]

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

maxxsqlserver
Starting Member

6 Posts

Posted - 2007-11-27 : 06:47:20

quote:
Originally posted by khtan

select COUNT(DISTINCT(serial_number)) AS totale
from riparazioni_ict
where data >= @date
and data < dateadd(day, 1, @date)

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

THANK YOU SO MUCH!!

Maxx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-27 : 07:46:40

quote:
Originally posted by khtan

[code]select COUNT(DISTINCT(serial_number)) AS totale
from riparazioni_ict
where data >= dateadd(day,0,datediff(day,0,@date),0)
and data < dateadd(day,0,datediff(day,0,@date),1)
KH
[spoiler]Time is always against us[/spoiler]

Safer side

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 09:14:43

quote:
Originally posted by madhivanan

Safer side

Madhivanan

Failing to plan is Planning to fail

then why not the "data < " part ?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-27 : 09:40:13

quote:
Originally posted by khtan

quote:
Originally posted by madhivanan

Safer side

Madhivanan

Failing to plan is Planning to fail

then why not the "data < " part ?

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

Yes It should also be

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources