After restoring our database on another computer dates changed!

Question

---

We restored our database, which was on SQL Server 2005, to SQL Server 2008 on another computer. This new computer has Windows 2008 on it and is a server computer.  The problem has arised recently with the query: When we query SQL Server for the transactions between some dates it is giving other transactions outside that dates as well. For example, when we query the system with the date Between 12/1/2011 AND 12/31/211, it is giving all the transactions between those dates and also some of the transactions in 1/1/2012.  It is only giving wrong transactions in the next day and not more, and the next day transaction are not all the transactions in that day.  It seems that the mistake happens only if the filtering of dates is in the last day of the month or the first day of the month; for example if the dates are between 31/1/2012 ad 31/1/2012 the query also returns some of the transaction in 31/2/2012 and also if the quesry uses date filter between 1/1/2012 and 1/1/2012 the quesry also returns 1/2/2012 which is the next day!

Is the database corrupt? What will be the solutions?

Answer 1

could you post that query 

---

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 2

My guess would be the default date format is different between the 2 servers.  You should never depend on the implicit conversion of strings to dates, you should always use the CONVERT function with the correct format code.

Answer 3

Check the regional settings for your servers.

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

Answer 4

Thanks for the answer; we will test to see it.  But I the query the application always get the ToString(CurrentCulture) of the date. The velue for the CurrentCulture is always fixed and hard coded; I wonder if this could be a problem? All the reports in the applications use this settings. But it seems that the ShortDatePattern is not specified! Here is one Example:

   

TrxDate BETWEEN from.ToString(CurrentCulture)  AND

to.ToString(CurrentCulture)

   

public static CultureInfo CurrentCulture

        {

get

            {

                dateFormat.LongDatePattern = "yyyy-MM-dd"

                dateFormat.LongTimePattern =

"HH:mm:ss";//Note that we cannot use Millisecond or ff, since it creates concurrency viloations

                currentCulture.DateTimeFormat = dateFormat;

                currentCulture.NumberFormat.CurrencyDecimalSeparator =

".";

                currentCulture.NumberFormat.NumberDecimalSeparator =

".";

return currentCulture;

            }

        }

---

Mike

Answer 5

Hi Mike,

This issue may be related with the time zoon setting. Please check this setting on the server.It is also suggested to use UTC time to avoid this issue.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 6

Hi,

Thank you for the help.

We have changed the Windows 2008 time zone to be the the same as the client but it is still giving this wrong result. Also about UTC time, you mean I should convert every date of the query I send it to the SQL Server to be a UTC?

Thanks,

Mike

---

Mike

Answer 7

Please post the results of SELECT @@LANGUAGE from both servers.  The default date format is controlled by the language selected.

Answer 8

Or more appropriately:

sp_helplanguage @@LANGUAGE