I am using Sql Server 7 on an Internet Web Application and am stuck on one particular search using date fields.
I basicall want to grab all records in a table where a date field is between 2 passed date variables. It just isn't working correctly!!
My server is UK based and the dates are stored in dd/mm/yyyy format. If I simply pass 2 dates in as entered in this format the search returns 0 results. What should I do to rectify this.
My query is as follows:
SELECT dbo.Notes.ID, dbo.Notes.Ndate, dbo.Notes.Notes, dbo.Notes.TransID, dbo.Seller.Postcode, dbo.Trans.SellerID
FROM dbo.Notes, dbo.Seller, dbo.Trans
WHERE dbo.Notes.BranchID = BID AND (dbo.Notes.NDate >= fdate AND dbo.Notes.NDate <= tdate) AND dbo.Trans.TransactionID = dbo.Notes.TransID AND dbo.Seller.SellerID = dbo.Trans.SellerID
ORDER BY Ndate
This query worked fine untill I added the date search into it.
If I pass fdate as 31/05/2001 and the end date as 06/07/2001 three records should be returned but none are. I have tried converting the date vars to American format of mm/dd/yyyy and still no results. The only time the query returns anything is if I pass the fdate as american style and the tdate as getdate() function. But even then this returns all the records, not the ones between the dates I set.