Date problem with SQL 2000
-
I 've been using SQL for two years now and used a lot of date pararmeters to retrieve records between @startdate and @enddate.
I recently added a new audit date which is being automatically set by VB as the user checks audited mark. Now when I run a parameter based SP between @Startdate and @Enddate, it doen not return records audited at the Enddate but always returns one day less. All other SP are running Ok execpt for that field. I looked into the table to see how the datetime was put and everything looks OK on table level.
As a work around I am using the Left function which solves the problem. However, my concern is why it is happening and should I then revise the rest of the procedures in my data base ( a huge one)
-
It seems that you are not sure of the root of the problem - is it VB, or is it SQL Server? I suggest that you run Profiler to see exactly what SQL statements the VB app is generating and then decide what to do from there. Also please explain how the left() function provides you with a workaround.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
When I ran the procedure from query analyzer, I still get the same erroneous results.
I use the left function as follows Left(datetimefield, 12) and it gives the accurate results.
-
But the left() function returns a varchar, not a datetime, so there's something strange going on here
Perhaps your problem is linked to the fact that SQL Server stores datetimes, not dates. Are you allowing for the time component in your code?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Yes, because I looked into the data at the table level and date is set correctly and it contains time.
About the left function, it is working perfectly. I got it from SQL bible book
-
OK - we're going to have to go into a bit more detail then. Can you please post examples of the query that you are using, the source data, the expected results and the actual results? I only need to see the critical fields - the ones that appear in the JOIN and WHERE conditions (and, of course, the audit date itself).
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
This is the original function that returns no results as audit dates are on 1/8/05
SELECT TOP 100 PERCENT ISNULL(dbo.tblMATransfer.tServiceDate, dbo.tblMAFolder.ServiceDate) AS ServiceDate, ISNULL(dbo.tblMATransfer.tStatus,dbo.tblMAFolder.FolderStatus) AS FolderStatus, ISNULL(dbo.tblMAFolder.MAFolderID, dbo.tblMATransfer.MATransferID) AS FolderID,
ISNULL(dbo.tblMATransfer.tCompany, dbo.tblMAFolder.CompanyCode) AS CompanyCode, ISNULL(dbo.tblMAFolder.AuditDate,
dbo.tblMATransfer.AuditDate) AS AuditDate
FROM dbo.tblMATransfer LEFT OUTER JOIN
dbo.tblMAFolder ON dbo.tblMATransfer.MAFolderID = dbo.tblMAFolder.MAFolderID
WHERE (ISNULL(dbo.tblMAFolder.IsAudited, dbo.tblMATransfer.IsAudited) = 1) AND (ISNULL(dbo.tblMAFolder.AuditDate, dbo.tblMATransfer.AuditDate) BETWEEN
@startdate AND @enddate)
ORDER BY ISNULL(dbo.tblMATransfer.tServiceDate, dbo.tblMAFolder.ServiceDate)
This is the text after using the LEFT FunctionSELECT TOP 100 PERCENT ISNULL(dbo.tblMATransfer.tServiceDate, dbo.tblMAFolder.ServiceDate) AS ServiceDate, ISNULL(dbo.tblMATransfer.tStatus,dbo.tblMAFolder.FolderStatus) AS FolderStatus, ISNULL(dbo.tblMAFolder.MAFolderID, dbo.tblMATransfer.MATransferID) AS FolderID,
ISNULL(dbo.tblMATransfer.tCompany, dbo.tblMAFolder.CompanyCode) AS CompanyCode, LEFT(ISNULL(dbo.tblMAFolder.AuditDate,
dbo.tblMATransfer.AuditDate), 12) AS AuditDate
FROM dbo.tblMATransfer LEFT OUTER JOIN
dbo.tblMAFolder ON dbo.tblMATransfer.MAFolderID = dbo.tblMAFolder.MAFolderID
WHERE (ISNULL(dbo.tblMAFolder.IsAudited, dbo.tblMATransfer.IsAudited) = 1) AND (LEFT(ISNULL(dbo.tblMAFolder.AuditDate, dbo.tblMATransfer.AuditDate), 12)
BETWEEN @startdate AND @enddate)
ORDER BY ISNULL(dbo.tblMATransfer.tServiceDate, dbo.tblMAFolder.ServiceDate)
When I run this function I get the following correct data:ServiceDate FolderStatus FolderID CompanyCode AuditDate 01/07/2005 Closed 166881 195 Aug 1 2005 03/07/2005 Closed 168293 147 Aug 1 2005 03/07/2005 Closed 9966 356 Aug 1 2005 05/07/2005 Closed 9842 160 Aug 1 2005 05/07/2005 Closed 168308 195 Aug 1 2005 07/07/2005 Closed 168307 195 Aug 1 2005 08/07/2005 Closed 168617 160 Aug 1 2005 08/07/2005 Closed 9955 160 Aug 1 2005 10/07/2005 Closed 9846 19 Aug 1 2005 17/07/2005 Closed 168765 195 Aug 1 2005 17/07/2005 Closed 168846 195 Aug 1 2005 21/07/2005 Closed 10301 77 Aug 1 2005 -
To get those results, what did you put in @StartDate and @EndDate? Was it 1/8/2005?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
One issue with DATETIME is that it includes the TIME. If you don't supply a time, then the default is 00:00:00.000.
So, BETWEEN '2005-08-09' AND '2005-08-11' is actually BETWEEN '2005-08-09 00:00:00.000' AND '2005-08-11 00:00:00.000'
Which means all of the 9th and all of the 10th and none of the 11th.
Using LEFT lets you compare just the date.
One solution is to change your second value to: AND @endate + 1.
-SQLBill
-
@startdate 31/7/05 and @enddate 1/8/05
-
Then SQLBill's post is 100% relevant.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Of course this will work, but the question is that I already have a lot of procedures based on the same formula but never experienced such problem. So, I am a little bit confused
The other thing is that if I am to do this, should I correct the other procedures which so far were giving correct results?
-
Yes - I would advise you to amend the other procedures to deal with this.
The only reason I can think of as to why they seem to work already is that the datetime fields they are querying have 00:00 time components, whereas your new audit field has a non-zero time.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Like I said, you asked for everything from 31/7/05 00:00:00.00 up to 1/8/05 00:00:00, so you won't get any values from the 1st of August.
Either include the time or go one day more.
-SQLBill
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply