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
LVL 2
hboysAsked:
Who is Participating?
 
Jeremy_DConnect With a Mentor Commented:
<slap forehead>

I think I have it. The Month and Day functions probably return one digit numbers. Try your date-formatting like this:

nfdate = CStr(Year(fdate)) + Right("00" + Cstr(Month(fdate)), 2) + Right("00" + Cstr(Day(fdate)), 2)

Otherwise a date like 3 Januari 2001 will come out as "200113", while it should be "20010103" of course.
0
 
acampomaCommented:
listening...
0
 
lozzamooreCommented:
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 convert(char(8), tdate,112)
AND
dbo.Trans.TransactionID = dbo.Notes.TransID
AND
dbo.Seller.SellerID = dbo.Trans.SellerID
ORDER BY Ndate


Hope this helps.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jeremy_DCommented:
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.
0
 
lozzamooreCommented:
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 convert(char(8), tdate,112)
AND
dbo.Trans.TransactionID = dbo.Notes.TransID
AND
dbo.Seller.SellerID = dbo.Trans.SellerID
ORDER BY Ndate


Hope this helps.
0
 
hboysAuthor Commented:
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) and convert(char(8), tdate,112) AND  dbo.Trans.TransactionID   = dbo.Notes.TransID AND  dbo.Seller.SellerID = dbo.Trans.SellerID
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
0
 
hboysAuthor Commented:
P.S.

Whats the set @@language in BOL. all about?

Harry
0
 
Jeremy_DCommented:
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?
0
 
hboysAuthor Commented:
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.Recordset")
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.Source)
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
0
 
Jeremy_DCommented:
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???
0
 
hboysAuthor Commented:
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.

0
 
Jeremy_DCommented:
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.
0
 
hboysAuthor Commented:
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
0
 
Jeremy_DCommented:
<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>
0
 
Jeremy_DCommented:
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".
0
 
hboysAuthor Commented:
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
0
 
Jeremy_DCommented:
Good. I'll put off my hat and lay down my pipe and magnifying glass now to enjoy my companies yearly barbeque :)

Cheers,
Jeremy
 
0
 
hboysAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.