hboys
asked on
Date Searches in SQL7
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.
Help!
H
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.
Help!
H
listening...
SQL Server stores its date's internally in its own ISO format, that has no ambiguity.
The date format returned and queried can be more to do with clients settings therefore. Check out the set @@language in BOL.
You could try converting your dates to get round all of these probs. And you can replace >= and <= with between that tends to be quicker.
Something similar to:
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
(convert(char(8), dbo.Notes.NDate, 112)
between convert(char(8),fdate,112)
AND
dbo.Trans.TransactionID = dbo.Notes.TransID
AND
dbo.Seller.SellerID = dbo.Trans.SellerID
ORDER BY Ndate
Hope this helps.
The date format returned and queried can be more to do with clients settings therefore. Check out the set @@language in BOL.
You could try converting your dates to get round all of these probs. And you can replace >= and <= with between that tends to be quicker.
Something similar to:
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
(convert(char(8), dbo.Notes.NDate, 112)
between convert(char(8),fdate,112)
AND
dbo.Trans.TransactionID = dbo.Notes.TransID
AND
dbo.Seller.SellerID = dbo.Trans.SellerID
ORDER BY Ndate
Hope this helps.
Format you dates as follows:
"yyyymmdd" ("yyyymmdd Hh:Mm:Ss" if you need the time)
and send them to the server as char or varchar. -> you will never have date-problems again.
"yyyymmdd" ("yyyymmdd Hh:Mm:Ss" if you need the time)
and send them to the server as char or varchar. -> you will never have date-problems again.
SQL Server stores its date's internally in its own ISO format, that has no ambiguity.
The date format returned and queried can be more to do with clients settings therefore. Check out the set @@language in BOL.
You could try converting your dates to get round all of these probs. And you can replace >= and <= with between that tends to be quicker.
Something similar to:
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
(convert(char(8), dbo.Notes.NDate, 112)
between convert(char(8),fdate,112)
AND
dbo.Trans.TransactionID = dbo.Notes.TransID
AND
dbo.Seller.SellerID = dbo.Trans.SellerID
ORDER BY Ndate
Hope this helps.
The date format returned and queried can be more to do with clients settings therefore. Check out the set @@language in BOL.
You could try converting your dates to get round all of these probs. And you can replace >= and <= with between that tends to be quicker.
Something similar to:
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
(convert(char(8), dbo.Notes.NDate, 112)
between convert(char(8),fdate,112)
AND
dbo.Trans.TransactionID = dbo.Notes.TransID
AND
dbo.Seller.SellerID = dbo.Trans.SellerID
ORDER BY Ndate
Hope this helps.
ASKER
lozzamoore,
I have tried altering my query as you said to:
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 (convert(char(8), dbo.Notes.NDate, 112)
between convert(char(8),fdate,112)
ORDER BY Ndate
But All I get is an error of 'Incorrect syntax near the word ORDER'
Have I missed something out.
I also tried changing the dates I was passing into the query to yyyymmdd and this didnt work either.
Cheers
Harry
I have tried altering my query as you said to:
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 (convert(char(8), dbo.Notes.NDate, 112)
between convert(char(8),fdate,112)
ORDER BY Ndate
But All I get is an error of 'Incorrect syntax near the word ORDER'
Have I missed something out.
I also tried changing the dates I was passing into the query to yyyymmdd and this didnt work either.
Cheers
Harry
ASKER
P.S.
Whats the set @@language in BOL. all about?
Harry
Whats the set @@language in BOL. all about?
Harry
It's "SET LANGUAGE" (no @@), and it's about setting the language for the current connection (overriding the server and login settings).
It seems to me that the client side is screwing up in some way. If you pass dates as I described them it *cannot* go wrong, regardless of language settings, so you client code must be mangling the dates before it passes them to SQL Server. Can you post the client-code?
It seems to me that the client side is screwing up in some way. If you pass dates as I described them it *cannot* go wrong, regardless of language settings, so you client code must be mangling the dates before it passes them to SQL Server. Can you post the client-code?
ASKER
Jeremy D,
Here is the ASP code I am using to run the SQL query:
<%
Dim getnotes__BID
getnotes__BID = "0"
if (Session("BranchID") <> "") then getnotes__BID = Session("BranchID")
%>
<%
Dim getnotes__fdate
fdate = Date() - 7
nfdate = Cstr(Month(fdate)) + "/" + CStr(Day(fdate)) + "/" + Cstr(Year(fdate))
getnotes__fdate = nfdate
if (Request("fromdate") <> "") then
fdate = CDate(Request("fromdate"))
nfdate = CStr(Day(fdate)) + "/" + Cstr(Month(fdate)) + "/" + Cstr(Year(fdate))
'nfdate = CStr(Year(fdate)) + "/" + Cstr(Month(fdate)) + "/" + Cstr(Day(fdate))
getnotes__fdate = nfdate
end if
%>
<%
Dim getnotes__tdate
getnotes__tdate = "getdate()"
if (Request("todate") <> "") then
fdate = CDate(Request("todate"))
nfdate = CStr(Day(fdate)) + "/" + Cstr(Month(fdate)) + "/" + Cstr(Year(fdate))
' nfdate = CStr(Year(fdate))+ "/" + Cstr(Month(fdate))+ "/" + Cstr(Day(fdate))
getnotes__tdate = nfdate
end if
%>
<%
set getnotes = Server.CreateObject("ADODB
getnotes.ActiveConnection = MM_Deedspack_STRING
getnotes.Source = "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 = " + Replace(getnotes__BID, "'", "''") + " AND (dbo.Notes.NDate >= "+getnotes__fdate+" AND dbo.Notes.NDate <= " + Replace(getnotes__tdate, "'", "''") + ") AND dbo.Trans.TransactionID = dbo.Notes.TransID AND dbo.Seller.SellerID = dbo.Trans.SellerID ORDER BY Ndate"
getnotes.CursorType = 0
getnotes.CursorLocation = 2
getnotes.LockType = 3
response.write(getnotes.So
getnotes.Open()
getnotes_numRows = 0
%>
I am manually converting the dates that are typed in by the user into the format of my choice by using the vbscript Day Month and year functions which return the day number, the month number and a 4 digit year.
Any ideas?
Cheers,
Harry
Here is the ASP code I am using to run the SQL query:
<%
Dim getnotes__BID
getnotes__BID = "0"
if (Session("BranchID") <> "") then getnotes__BID = Session("BranchID")
%>
<%
Dim getnotes__fdate
fdate = Date() - 7
nfdate = Cstr(Month(fdate)) + "/" + CStr(Day(fdate)) + "/" + Cstr(Year(fdate))
getnotes__fdate = nfdate
if (Request("fromdate") <> "") then
fdate = CDate(Request("fromdate"))
nfdate = CStr(Day(fdate)) + "/" + Cstr(Month(fdate)) + "/" + Cstr(Year(fdate))
'nfdate = CStr(Year(fdate)) + "/" + Cstr(Month(fdate)) + "/" + Cstr(Day(fdate))
getnotes__fdate = nfdate
end if
%>
<%
Dim getnotes__tdate
getnotes__tdate = "getdate()"
if (Request("todate") <> "") then
fdate = CDate(Request("todate"))
nfdate = CStr(Day(fdate)) + "/" + Cstr(Month(fdate)) + "/" + Cstr(Year(fdate))
' nfdate = CStr(Year(fdate))+ "/" + Cstr(Month(fdate))+ "/" + Cstr(Day(fdate))
getnotes__tdate = nfdate
end if
%>
<%
set getnotes = Server.CreateObject("ADODB
getnotes.ActiveConnection = MM_Deedspack_STRING
getnotes.Source = "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 = " + Replace(getnotes__BID, "'", "''") + " AND (dbo.Notes.NDate >= "+getnotes__fdate+" AND dbo.Notes.NDate <= " + Replace(getnotes__tdate, "'", "''") + ") AND dbo.Trans.TransactionID = dbo.Notes.TransID AND dbo.Seller.SellerID = dbo.Trans.SellerID ORDER BY Ndate"
getnotes.CursorType = 0
getnotes.CursorLocation = 2
getnotes.LockType = 3
response.write(getnotes.So
getnotes.Open()
getnotes_numRows = 0
%>
I am manually converting the dates that are typed in by the user into the format of my choice by using the vbscript Day Month and year functions which return the day number, the month number and a 4 digit year.
Any ideas?
Cheers,
Harry
So you have tried this:
nfdate = CStr(Year(fdate)) + Cstr(Month(fdate)) + Cstr(Day(fdate))
and then passing the dates as varchars by surrounding them with single quotes:
getnotes.Source = "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 = " + Replace(getnotes__BID,
"'", "''") + " AND (dbo.Notes.NDate >= '"+getnotes__fdate+"' AND dbo.Notes.NDate <= '" + getnotes__tdate + "') AND dbo.Trans.TransactionID = dbo.Notes.TransID AND dbo.Seller.SellerID = dbo.Trans.SellerID
ORDER BY Ndate"
(note that I removed the Replace around getnotes_tdate, since it seems unlikely that a single quote will ever turn up in a date)
No success with that???
nfdate = CStr(Year(fdate)) + Cstr(Month(fdate)) + Cstr(Day(fdate))
and then passing the dates as varchars by surrounding them with single quotes:
getnotes.Source = "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 = " + Replace(getnotes__BID,
"'", "''") + " AND (dbo.Notes.NDate >= '"+getnotes__fdate+"' AND dbo.Notes.NDate <= '" + getnotes__tdate + "') AND dbo.Trans.TransactionID = dbo.Notes.TransID AND dbo.Seller.SellerID = dbo.Trans.SellerID
ORDER BY Ndate"
(note that I removed the Replace around getnotes_tdate, since it seems unlikely that a single quote will ever turn up in a date)
No success with that???
ASKER
I have tried that but I get the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I'm stumped. The yyyymmdd format is ISO standard and will be recognised by SQL Server (and about any other RDBMS) regardles of language settings. Also, conversion from varchar to date is implicit, so that can't be a problem either.
Your error message seems to suggest that you are passing invalid dates, but I assume you have checked that.
Can you put the SQL statement in a string variable first and do a Response.Write to output the complete SQL string just before it is send to the server? Please post that complete string here so we can investigate further.
Your error message seems to suggest that you are passing invalid dates, but I assume you have checked that.
Can you put the SQL statement in a string variable first and do a Response.Write to output the complete SQL string just before it is send to the server? Please post that complete string here so we can investigate further.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is the sql statement using the yyyymmdd when the values have been inserted:
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 = 106 AND (dbo.Notes.NDate >= 200061 AND dbo.Notes.NDate <= 200168) AND dbo.Trans.TransactionID = dbo.Notes.TransID AND dbo.Seller.SellerID = dbo.Trans.SellerID ORDER BY Ndate
This is without the single quotes in the sql as I just get an error when they are inserted
What If I add an onError Resume statement to my ASP code and see if it will pass the single quotes?
Harry
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 = 106 AND (dbo.Notes.NDate >= 200061 AND dbo.Notes.NDate <= 200168) AND dbo.Trans.TransactionID = dbo.Notes.TransID AND dbo.Seller.SellerID = dbo.Trans.SellerID ORDER BY Ndate
This is without the single quotes in the sql as I just get an error when they are inserted
What If I add an onError Resume statement to my ASP code and see if it will pass the single quotes?
Harry
<sidenote>
I notice you are using "+" to concatenate your strings. I'm not that big of an ASP/VBS guru, but in VB you use the "+" operator for numeric additions, and the "&" operator for string concatenations. If you use "+" for string concatenations, you will sooner or later run into VB's "Evil Type Coercion", which seems even more likely to happen in VBS since you can't specifically declare variables as string. Not sure if ASP/VBS also has this distinction between "+" and "&" though, so check your documentation first.
</sidenote>
I notice you are using "+" to concatenate your strings. I'm not that big of an ASP/VBS guru, but in VB you use the "+" operator for numeric additions, and the "&" operator for string concatenations. If you use "+" for string concatenations, you will sooner or later run into VB's "Evil Type Coercion", which seems even more likely to happen in VBS since you can't specifically declare variables as string. Not sure if ASP/VBS also has this distinction between "+" and "&" though, so check your documentation first.
</sidenote>
2 more comments:
1. Your output shows you didn't put single quotes {'} around your dates. You need to do that (see my example code from 7:21 PST, paste in an editor to see the single quotes).
2. My <slap forehead> comment seems to be correct, as I see values like "200061" and "200168" which should obviously be "20000601" and "20010608".
1. Your output shows you didn't put single quotes {'} around your dates. You need to do that (see my example code from 7:21 PST, paste in an editor to see the single quotes).
2. My <slap forehead> comment seems to be correct, as I see values like "200061" and "200168" which should obviously be "20000601" and "20010608".
ASKER
Jeremy,
Your a genius!!!!!
Adding the extra 0 where there are one digit day/months allowed me to use the single quotes and return the correct date fields.
Thanks a lot for all your help,
Harry
Your a genius!!!!!
Adding the extra 0 where there are one digit day/months allowed me to use the single quotes and return the correct date fields.
Thanks a lot for all your help,
Harry
Good. I'll put off my hat and lay down my pipe and magnifying glass now to enjoy my companies yearly barbeque :)
Cheers,
Jeremy
Cheers,
Jeremy
ASKER
I have heard about the + versus & before but have only run into trouble using Javascript. I will however get into the habit of using the & operator for strings.
Again Many thanks for the help
Harry
Again Many thanks for the help
Harry