3

What could be the cause why GETDATE() is yielding different result when executed on Query Editor in SSMS (SQL Server Management Studio) versus from the one executed through Microsoft.Practices.EnterpriseLibrary.Data by Database.ExecuteNonQuery() function?

This GETDATE() is inside a Stored Procedure that UPDATE the date in a table.

Debug by us developers on SSMS:


    EXEC [dbo].[SP_NameOfTheSPWithGetDate]()

= 11/4/2016 7:43 PM

Being run through the ASP.net webpage by end-user:

Database SampleDB;
SampleDB.ExecuteNonQuery('[dbo].[SP_NameOfTheSPWithGetDate]');

= 11/4/2016 11:43 AM

I suspect the difference might be because of the +8 offset of the GMT (our server is in GMT+8) they run the same exact SP but why the different result from GETDATE()?

We tried all sorts of functions to get the current date like:

  • GETUTCDATE()
  • CURRENT_TIMESTAMP
  • GETDATE()
  • GETSYSTEMDATE()

But still we have the same result.

This problem is weird since I don't really pass any value to the SP that may ever affect the GETDATE() function.

If you need additional info, feel free to ask to help solve this problem.

Additional Information (Update) :

  • The one hosting the ASP.Net page server is different from the SQL Server Database.
jestrange
  • 271
  • 3
  • 15
  • Could be the user browser correcting the datetime rather than something happening in your backend? – user1666620 Nov 04 '16 at 11:58
  • I don't think so, the one calling the SP is backend C#. – jestrange Nov 04 '16 at 12:00
  • Check this question, and especially the (very long) accepted answer, perhaps it can shed some light: http://stackoverflow.com/q/2783348/1220550 – Peter B Nov 04 '16 at 12:03
  • @PeterB I think it's different because that particular Q&A was for ADO.Net but for this one, I think Microsoft.Practices.EnterpriseLibrary.Data might be different at the very least. – jestrange Nov 04 '16 at 12:09
  • Are you able to check in the profiler that the query executed against the database is precisely what you execute from SSMS? Are you sure you are connected to the same SQL instance from SSMS? – Jakub Szumiato Nov 04 '16 at 12:19
  • Be careful because some libraries might assume you want to localize your dates and times and perform time zone conversions. If you did not explicitly apply time zone conversions then check you application's Thread.CurentCulture and look for the offset. If it is the same time as the difference you are seeing then that library is probably applying conversions. – Ross Bush Nov 04 '16 at 12:22
  • 1
    Can you post the complete code of how you are getting the asp.net value out of the dataset? – Ross Bush Nov 04 '16 at 12:44
  • `GETDATE` always returns the local time on the SQL Server. As others have suggested, this is almost certainly a display issue, but it's unclear from the question where/how you're checking the date. What's the value in the table you mentioned that `SP_NameOfTheSPWithGetDate` updates? – Ed Harper Nov 04 '16 at 12:48
  • Are the SQL Server / IIS server times different or are they in different time zones? Those functions are getting times from two different servers – Nick.Mc Nov 04 '16 at 14:33

2 Answers2

0

Maybe try SYSDATETIMEOFFSET()

So you could check if different timezones causes the problem - if yes:

complete solution/all credits: Getdate() function to get date for my timezone

Community
  • 1
  • 1
tgr
  • 244
  • 1
  • 9
0

Since both will be executed on SQL Server, the result will be the same, but the second one will be returned and handled according to the .Net current culture and data format, which in your case seems different.

If you set the culture of your .Net application to something else, for ex: ar-sa, it will show the data in Hijri format which will be totally something different :)

Haitham Shaddad
  • 4,336
  • 2
  • 14
  • 19