SQL Server 2005 Date/time Problem, Could someone please help me.

• rox_thegirl3

  Newbie

  Points: 1

  More actions

  August 7, 2009 at 9:56 am

  #135187

  Hello,

  I have two issues and I hope someone can help me with it. I have been on this problem for 3 days now, And I can't resolve it.

  #1 Issue

  I have a database in access 2003 that is the front-end and the backend on sql server 2005.

  I have a table called group_section, that has 3 fields begintime, endtime and date.

  Before I linked the access application with sql server the data type of all 3 were as follow: begintime was date/time, medium time; endtime was date/time medium time; and the date was date/time medium date.

  When I upsized the database, the datetypes in sql server shows the fields data as folow:

  # the date shows the output as 11/10/2005 12:00:00 AM instead of 11-Oct-05

  # the begintime output shows as 12/30/1899 6:00:00 PM instead of 6:00 PM

  # the endtime shows the output as 12/30/1899 9:00:00 PM instead of 9:00 PM

  1. How can I change the date/time to be the same as in access ?????

  Thanks

• Jack Corbett

  SSC Guru

  Points: 184397

  More actions

  August 7, 2009 at 1:19 pm

  #1036257

  SQL Server 2005 only has 2 date types, smalldatetime and datetime, both of which store date and time. By default if you do not enter a time then you get midnight as the time portion and if you do not enter a date you get 12/30/1899 + the time.

  I'd recommend a re-design to use just begintime and endtime as you really no longer need the date. If that is not acceptable or doable then I'd run an update statement that sets the date part of endtime and begintime to the appropriate day. It would be something like this:

  DECLARE @table TABLE (begintime DATETIME, endtime DATETIME, date SMALLDATETIME)

  INSERT INTO @table (

  begintime,

  endtime,

  date

  )

  SELECT

  '12/30/1899 18:00:00',

  '21:00:00',

  '8/6/2009'

  SELECT * FROM @table

  UPDATE @table

  SET begintime = DATEADD(DAY, DATEDIFF(DAY, begintime, date), begintime),

  endtime = DATEADD(DAY, DATEDIFF(DAY, endtime, date), endtime)

  SELECT * FROM @table

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question