Date Filter

Question

Post reply

Date Filter

2Tall

SSChampion

Points: 14644
More actions
October 7, 2009 at 11:51 am

#385526

I want to return records where EffectiveDate is greater than 30/09/2009 (30th September 2009).
I have:
[Code]
SELECT CONVERT(NVARCHAR,i.CreatedDate,103) AS CreatedDate,CONVERT(NVARCHAR,i.EffectiveDate,103) AS EffectiveDate
FROM Inventory AS i
WHERE i.InventoryReasonCode = 173 AND CONVERT(NVARCHAR,i.EffectiveDate,103) < '30/09/2009'
ORDER BY EffectiveDate ASC
[/Code]
This returns:
[Quote]
CreatedDate EffectiveDate
------------------------------ ------------------------------
07/10/2009 07/10/2009
07/10/2009 07/10/2009
07/10/2009 07/10/2009
07/10/2009 07/10/2009
07/10/2009 07/10/2009
07/10/2009 07/10/2009
07/10/2009 29/09/2009
07/10/2009 29/09/2009
07/10/2009 29/09/2009
[/Quote]
If I change filter to < '30/09/2009' the query returns no rows.
Bit miffed.....
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What does this return:

SELECT

i.CreatedDate,

i.EffectiveDate

FROM

Inventory AS i

WHERE

i.InventoryReasonCode = 173

AND i.EffectiveDate < '01/10/2009'

ORDER BY

EffectiveDate ASC

2Tall SSChampion Points: 14644 More actions · Answer 2

0 rows......

Thanks Lynn.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

October 7, 2009 at 12:06 pm

#1062978

For S and G's change my query to use '20091001'.

2Tall SSChampion Points: 14644 More actions · Answer 4

Thanks Lynn. This worked:

[Code]

SELECT

i.CreatedDate,

i.EffectiveDate

FROM

Inventory AS i

WHERE

i.InventoryReasonCode = 173

AND i.EffectiveDate > '20091001'

ORDER BY

EffectiveDate ASC

[/Code]

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper