Solved

Date Searches in SQL7

Posted on 2001-06-08
18
209 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:hboys
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 6

Expert Comment

by:acampoma
ID: 6167435
listening...
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 6167441
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
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6167543
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
 
LVL 7

Expert Comment

by:lozzamoore
ID: 6167568
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
 
LVL 2

Author Comment

by:hboys
ID: 6167776
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
 
LVL 2

Author Comment

by:hboys
ID: 6167782
P.S.

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

Harry
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6167803
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
 
LVL 2

Author Comment

by:hboys
ID: 6167834
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
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6167905
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Author Comment

by:hboys
ID: 6167937
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
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6167970
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
 
LVL 4

Accepted Solution

by:
Jeremy_D earned 100 total points
ID: 6167983
<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
 
LVL 2

Author Comment

by:hboys
ID: 6167990
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
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6168002
<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
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6168016
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
 
LVL 2

Author Comment

by:hboys
ID: 6168017
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
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6168031
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
 
LVL 2

Author Comment

by:hboys
ID: 6168034
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now