Sql date field is not getting saved exactly

views:

answers:

+2 Q:

Sql date field is not getting saved exactly

Hi, I am trying to insert a date value '2010-03-14 02:00:00 AM'(Day light saving start time for 2010 year) in a datetime field of a table in sql server 2005. The date is saved as '2010-03-14 03:00:00 AM' The server is installed in US(EST time zone).

Can anyone tell me the reason why the date is saving differently?

Thanks Rupa

+2 A:

As the clock jumps from 0200 to 0300 one of these times does not exist.

Wikipedia says

A digital display of local time does not read 02:00 exactly at the shift, but instead jumps from 01:59:59.9 either forward to 03:00:00.0 or backward to 01:00:00.0.

In my view you should save all times in a database in GMT/UTC and only use the local time zone on display in the client - thus in the database all times will be monotonically increasing (that is the times will not jump back at the end of daylight saving)

Mark 2009-11-04 13:01:27

I am inserting records in DB in GMT format only.What my actual problem is I have first date(2009-10-25 08:00:00 AM) and last date(2009-11-07 09:00:00 AM).When these dates are saved in DB in GMT as firstdate(2009-10-25 12:00:00 PM) and lastdate(2009-11-07 02:00:00 PM). The time difference for firstdate and lastdate (in front end) is one hour and difference after saving in db is 2 hours. If i want to do any manipulation in db means, it giving odd results because of difference change for front end and DB.Any idea to sort out this issue?

Rupa 2009-11-04 13:17:49

I am confused. If yiou are entering "'2010-03-14 02:00:00 AM'(Day light saving start time for 2010 year) " that is not GMT as GMT does not have a daylight saving. (Also if using British times note that the time of change is 0100 not 0200)I would get the database set to GMT not EST. If not you will have to convert the input time from GMT to EST

Mark 2009-11-04 13:27:59

+2 A:

GMT != UTC. GMT has Daylight Savings, UTC does not. Save your dates as UTC and you're good to go.

ElHombre 2009-11-04 15:27:11

one point: GMT by definition does not change throughout the year: in Britain one exists either on GMT or on BST (British Summer Time).

That aside, I would recheck your time zone settings for your db and your server.

dcpking 2009-11-05 00:49:58

ansaurus

tags:

views:

answers:

Sql date field is not getting saved exactly

related questions