More date stuff!

[Ashkan]

Hello,

I need to create a query that searches the contract start dates, contract finish dates and returns the contract numbers of any contracts that INCLUDE the months 11 (November) and 12 (December).

This is what I have done so far:

SELECT ContractNumber
FROM tblContract
WHERE ContractStartDate = '##/11/####'
AND ContractEndDate = '##/12/####';



It doesn't seem to be working... any suggestions people?

Thanks
Ashkan

 

[ggreg]

give this a try */11/*
and */12/* inside that query

 

[Milothicus]

try this....

SELECT ContractNumber
FROM tblContract
WHERE month(ContractStartDate) = 11
AND month(ContractEndDate) = 12;

 

[Ashkan]

I used the following and it worked:

SELECT ContractNumber
FROM tblContract
WHERE month(ContractStartDate) = 11
AND month(ContractEndDate)=12;

But I need it to retrieve ANY records that INCLUDE the months 11 and 12, for example if the contract start date was 01/09/2004 and the contract end date was 30/01/2005 then it would have to be included in the results...

I hope this makes sense as I am really stuck!

Thanks
Ashkan

 

[WayneRyan]

AshKan,

You're not taking years into account, but:

SELECT ContractNumber
FROM tblContract
WHERE  month(ContractStartDate) = 11 Or
       month(ContractStartDate) = 11 Or
       month(ContractEndDate) = 12 Or
       month(ContractEndDate) = 12;

Wayne

 

[Ashkan]

Thanks Wayne

 

[WayneRyan]

Ashkan,

You really need to do this from the perspective of a date
range that the user enters:

SELECT ContractNumber
FROM tblContract
WHERE  (ContractStartDate >= [StartDate] And
        ContractStartDate <= [EndDate]) Or
       (ContractEndDate) >= [StartDate] And
        ContractEndDate) <= [EndDate])

That way if the contract starts OR ends within the user-entered
date window, it will be included.

Wayne