none
Access Now() vs. T-SQL GETDATE() ?

    Question

  • I am translating some Access 2007 queries that are running against linked SQL Server 2005 tables to T-SQL against the same tables.  I am having a problem with differing results that I can't figure out.

     

    This Access query return 10/4/08

     

    SELECT  Min(FinalSimulatorDate) AS MinOfFinalSimulatorDate

    FROM FactPanelHistory

    WHERE FinalSimulatorReadTime > Now() - 5

    GROUP BY Format(FinalSimulatorReadTime,"ww yy")

     

    This T-SQL query returns 10/2/08

     

    SELECT  Min(FinalSimulatorDate) AS MinOfFinalSimulatorDate

    FROM FactPanelHistory

    WHERE FinalSimulatorReadTime > GETDATE() - 5

    GROUP BY CONVERT(varchar,DATEPART(week, FinalSimulatorReadTime)) + ' ' + RIGHT(CONVERT(varchar,DATEPART(yy, FinalSimulatorReadTime)),2)

     

    In order to get the same results as Access, I have to do GETDATE() - 3 in T-SQL.

     

    Actually, given that today is 10/7/08, T-SQL's results intuitively make more sense.  One obvious thing that is weird is what I had to do to the group by - but I've tested the output of the format vs. convert expressions and they come out the same.

     

    Anyone know what the deal is with this?

    Tuesday, October 07, 2008 4:18 PM

Answers

  • Mark,

     

    Hmm.  this is interesting.  Since you are using linked table data is not the issue, the issue has to be the where clause.  The difference between the two functions is the milliseconds.  The milliseconds must be throwing off your where clause.  Try converting the getdate() to exclude milliseconds.

     

    like this. Note: 120 format is yyyy-mm-dd hh:miTongue Tieds

    Code Snippet

    SELECT  Min(FinalSimulatorDate) AS MinOfFinalSimulatorDate

    FROM FactPanelHistory

    WHERE FinalSimulatorReadTime > convert(varchar,GETDATE() - 5,120)

    GROUP BY CONVERT(varchar,DATEPART(week, FinalSimulatorReadTime)) + ' ' + RIGHT(CONVERT(varchar,DATEPART(yy, FinalSimulatorReadTime)),2)

     

     

     

    Tuesday, October 07, 2008 6:00 PM

All replies

  • The two date functions produce the same output. There is no difference between getdate() and now().  If the query results are different, their can only be a few problems.  Possible problems are the data is different between SQL and Access, or the query is not built the same (which does not seem to be the case here).  Is the SQL data imported from Access? 

     

    You can test now and getdate() but I assure you that the output is exactly the same, as I have even tested them locally on my laptop.

    Tuesday, October 07, 2008 4:36 PM
  • Can you please run this in

    -- Access: 
    SELECT Date() 
     
    -- SQL Server: 
    SELECT GETDATE()

     

    and give us the output

    Tuesday, October 07, 2008 5:15 PM
  • Adam - The data is the same.  The Access query is running against the same table as the SQL query.  The table is linked in Access.

     

    Ashwani -

     

    -- Access: 
    SELECT Date() 

     

    10/7/2008

     

    -- Access: 
    SELECT Now() 

     

    10/7/2008 1:28:27 PM

     

    -- SQL Server: 
    SELECT GETDATE()

     

    2008-10-07 13:28:32.177

    Tuesday, October 07, 2008 5:30 PM
  • Also, in the original Access query, I swapped Now() with Date() to see what happens, like this:

     

    SELECT  Min(FinalSimulatorDate) AS MinOfFinalSimulatorDate

    FROM FactPanelHistory

    WHERE FinalSimulatorReadTime > Date() - 5

    GROUP BY Format(FinalSimulatorReadTime,"ww yy")

     

    But I get the same result, 10/4/08

     

     

     

    Tuesday, October 07, 2008 5:34 PM
  • if you do date() it does not get time part .. and it is thus date same as getdate() 's date part

     

    Tuesday, October 07, 2008 5:38 PM
  • Mark,

     

    Hmm.  this is interesting.  Since you are using linked table data is not the issue, the issue has to be the where clause.  The difference between the two functions is the milliseconds.  The milliseconds must be throwing off your where clause.  Try converting the getdate() to exclude milliseconds.

     

    like this. Note: 120 format is yyyy-mm-dd hh:miTongue Tieds

    Code Snippet

    SELECT  Min(FinalSimulatorDate) AS MinOfFinalSimulatorDate

    FROM FactPanelHistory

    WHERE FinalSimulatorReadTime > convert(varchar,GETDATE() - 5,120)

    GROUP BY CONVERT(varchar,DATEPART(week, FinalSimulatorReadTime)) + ' ' + RIGHT(CONVERT(varchar,DATEPART(yy, FinalSimulatorReadTime)),2)

     

     

     

    Tuesday, October 07, 2008 6:00 PM
  • Adam,

     

    You are right – the problem was in the WHERE clause.  However, it was not related to the milliseconds.

     

    There were actually a couple of other fields in the WHERE clause that I omitted from this post for simplicity.  One of them was the FinalSimulatorPlant field.  They (the authors of the Access code) were using the Access parameter prompt syntax like this:

     

    FinalSimulatorPlant = [Enter Plant]

     

    So, I had been running it from Access’s Design View and when I hit run I would get a dialog box that said Enter Plant and I would enter 1.  This is how I was getting 10/4/08 as a result.

     

    Then after 4 hours of tearing my hair out on the issue, I finally went into Access’s SQL view to play around with the actual query text.

     

    When I replace [Enter Plant] with 1, as in FinalSimulatorPlant = 1, I got a type mismatch error.  So I went to the source table and found that the FinalSimulatorPlant field is varchar.

     

    So, then I entered FinalSimulatorPlant = “1” and it returned the correct result (and the same as SQL Server), 10/2/08.

     

    Then I went back and used the dialog box again and entered “1” into it.  But then it returned 0 records.  Then I tried ‘1’ in the dialog and also got 0 records.

     

    I don't know what that dialog box is doing, but unless I am missing something, it appears that if these people had been using the Access dialog box for the last 10 years, they have never gotten the right results.

     

    Tuesday, October 07, 2008 7:24 PM
  • Dialog boxes in MS Access is a way to filter a query with user input.  The imput is passed to the underlying table or query in the filter criteria.  The Input dialog box is denoted by brackets.  e.g. somecolumn = [SomeTextToPromptUserForInput]. The solution seems to go back to my main hunch that either the data was wrong or the two queries not not the same.  Sometimes you have to dig deep to find underlying problems. 

     

    I am glad you got everything settled.

     

    -Adam

    Tuesday, October 07, 2008 8:17 PM