Select datetime prints wrong date

views:

160

answers:

+1 Q:

Select datetime prints wrong date

When I select some data rows from mssql2005 database and bind them to datagrid, dates selected from db are invalid. For example in db I have date in form:

2009-05-10 00:00:00.000

but when it is displayed in data grid it looks like this:

5/9/2009 10:00:00 PM

So it shows 2 hours earlier then saved in db.

05 - it is month :-)

What am I doing wrong?

Records in db where added by insert sql query in Sql Management Studio, as these recods are just for testing.

I am using silverlight 3.0, ria services may preview, english vs08, polish MS vista home premium.

+1 A:

Looks like a timezone issue to me. Are you selecting the rows directly from the database into your application, or are you possibly retrieving them via a webservice from another machine?

As a basic test, try setting your windows timezone +2 hours ahead then see whether the issue still occurs.

Dan Fuller 2009-05-20 09:10:14

Directly from database into my application. Both - app and mssql server works on the same machine

Michal Krawiec 2009-05-20 09:12:47

Try my basic test and let me know how it goes. It's possible that your SQL Server might be running under a windows account that has a different timezone set than your account.

Dan Fuller 2009-05-20 09:14:25

After doing that it shows in dataGrid:5/9/2009 11:00:00 PMbut there is other issue, which I just noticed. These are three date rows taken from three different db rows:2009-05-10 00:00:00.0002009-04-10 00:00:00.0002009-03-10 00:00:00.000and the same rows presented in datagrid:5/9/2009 11:00:00 PM4/9/2009 11:00:00 PM3/10/2009 12:00:00 AMSo it shows different hour on march 10 2009.

Michal Krawiec 2009-05-20 10:36:38

Several countries begin their daylight savings time around mid-March, although I could only find ones for March 8th, not the 9th or 10th.

Tom H. 2009-05-20 10:49:20

Ok, that's interesting. By any chance did you only just add in the march 10 date after you changed your local timezone?

Dan Fuller 2009-05-20 10:53:45

Also, could you tell me what the type of your column is. Is it just a datetime?

Dan Fuller 2009-05-20 10:56:44

In Poland we are changing to DST (summer time) on 26th of march, so this could be the issue, why hours before march 26th are different. But still - what should I do to show time saved in database?

Michal Krawiec 2009-05-20 11:06:26

@Dan - I do not understand this post: 'Ok, that's interesting...' and yes my column in mssql is datetime

Michal Krawiec 2009-05-20 11:08:44

You posted "2009-05-10 00:00:00.000 2009-04-10 00:00:00.000 2009-03-10 00:00:00.000 and the same rows presented in datagrid: 5/9/2009 11:00:00 PM 4/9/2009 11:00:00 PM 3/10/2009 12:00:00 AM So it shows different hour on march 10 2009"I can't really see why these times would show as one thing in the database and another in the grid. As far as I know, and from some quick research, datetime doesn't store timezone information. However, I was wondering if the 3/10/2009 date that was showing up correctly was entered more recently than the other dates?

Dan Fuller 2009-05-20 11:14:42

Nope. I suppose it has something to do with time changes in Poland, which occures on 26th of March. Just minute ago I inserted few more rows into db and these with date after 26th of March shows -2h in datagrid. If not dataGrid (which I suppose should do nothing with data) than what can it be?

Michal Krawiec 2009-05-20 11:22:24

Ok, so just to confirm, if you have dates before 26th of March it shows in the grid just fine, but after the 26th it shows -2 in the grid, is that correct? Does your daylight savings change your timezone by 2 hours or 1?

Dan Fuller 2009-05-20 11:31:20

@Dan - no it is not correct. Dates before 26th of March are shown with -1h and dates after March 26th are shown with -2h. My daylight savings changes my timezone by 1 hour

Michal Krawiec 2009-05-20 11:41:32

Ok, that's pretty much what I thought. Your SQL Server runs under a system account - you can see which account this if you go find it under services. This account is probably running under a different timezone than you - thus the problems with timezone. You'll need to modify that account and set it to the correct timezone. I'll do some googling on this shortly, but am not sure how to fix this just now.

Dan Fuller 2009-05-20 12:46:51

Hi Michal. I believe that in Windows Vista it's possible to set different timezones per user. Unfortunately, I don't have a version of Vista installed that I can play around with, so I can't be as much help to you as I'd like.To resolve your problem, I believe you'll need to go to the services manager in Vista, find the SQL Server service, view its details and find the user it runs under, likely a system account. You'll then need to investigate how to change the timezone for this account to the same timezone you're running on.

Dan Fuller 2009-05-21 17:09:36

Hi Dan - First of all thank you for your time and help. I have found that sql server works with network service user account :-) I will try to deal with settings of this account and post results.

Michal Krawiec 2009-05-22 08:12:25

ansaurus

tags:

views:

answers:

Select datetime prints wrong date

related questions