12 Mart 2012 Pazartesi 14:28
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?
- Düzenleyen Mike9000 12 Mart 2012 Pazartesi 15:04
12 Mart 2012 Pazartesi 14:45could 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
12 Mart 2012 Pazartesi 17:26Moderatör
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.
12 Mart 2012 Pazartesi 18:44
13 Mart 2012 Salı 02:03
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
public static CultureInfo CurrentCulture
dateFormat.LongDatePattern = "yyyy-MM-dd"
"HH:mm:ss";//Note that we cannot use Millisecond or ff, since it creates concurrency viloations
currentCulture.DateTimeFormat = dateFormat;
13 Mart 2012 Salı 02:59Moderatör
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.
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.
13 Mart 2012 Salı 14:51
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?
14 Mart 2012 Çarşamba 17:17ModeratörPlease post the results of SELECT @@LANGUAGE from both servers. The default date format is controlled by the language selected.
14 Mart 2012 Çarşamba 17:19Moderatör
Or more appropriately: