Post reply

Date problem with SQL 2000

  • August 11, 2005 at 1:22 am

    #363375

    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)

  • August 11, 2005 at 2:15 am

    #581307

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • August 11, 2005 at 2:34 am

    #581310

    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.

  • August 11, 2005 at 2:59 am

    #581313

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • August 11, 2005 at 3:21 am

    #581315

    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

  • August 11, 2005 at 3:36 am

    #581318

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • August 11, 2005 at 4:15 am

    #581328

    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 Function
     

    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, 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:

    ServiceDateFolderStatusFolderIDCompanyCodeAuditDate
    01/07/2005Closed 166881195Aug 1 2005
    03/07/2005Closed 168293147Aug 1 2005
    03/07/2005Closed 9966356Aug 1 2005
    05/07/2005Closed 9842160Aug 1 2005
    05/07/2005Closed 168308195Aug 1 2005
    07/07/2005Closed 168307195Aug 1 2005
    08/07/2005Closed 168617160Aug 1 2005
    08/07/2005Closed 9955160Aug 1 2005
    10/07/2005Closed 984619Aug 1 2005
    17/07/2005Closed 168765195Aug 1 2005
    17/07/2005Closed 168846195Aug 1 2005
    21/07/2005Closed 1030177Aug 1 2005

  • August 11, 2005 at 5:49 am

    #581343

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • August 11, 2005 at 6:24 am

    #581359

    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

  • August 12, 2005 at 5:07 am

    #581698

    @startdate 31/7/05 and @enddate 1/8/05

  • August 12, 2005 at 5:10 am

    #581699

    Then SQLBill's post is 100% relevant.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • August 12, 2005 at 5:11 am

    #581700

    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?

  • August 12, 2005 at 5:14 am

    #581702

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • August 12, 2005 at 6:39 am

    #581725

    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