Date and Time Question

• dyckwalter

  SSC Enthusiast

  Points: 184

  More actions

  October 5, 2004 at 5:10 am

  #89454

  Hello Guys,

  I'm using ADO VB program to write data into SQL.

  I see that the date is formatted 'YYYY-MM-DD HH:MM:SS' in my VB program. But when I look in the database, the format =

  'YYYY-DD-MM HH:MM:SS'

  I can't think of anything so get this right. Maybe there is

  someone who can help me with this??

  Thanks

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  October 5, 2004 at 5:26 am

  #525358

  I would check the collation of the database and the table and ensure that it is set to a collation that would give you the proper DATETIME format.

  I would also look at the regional settings on the SQL Server and ensure that the DATETIME format is set correctly as well.

   

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  October 5, 2004 at 10:17 am

  #525456

  Very basically, dates and times are not STORED in SQL Server in any format (style). They are stored as an eight byte value.

  It's how they are DISPLAYED and inputted that cause the problems.

  Bottom line, the COLLATION of the database is how SQL Server interprets the datetime values for input (inserts and updates) and output (displaying).

  However, you can tell SQL Server how to interpret the values.

  For inputs, you include the command:

  SET DATEFORMAT DMY

  The DMY can be changed to the proper order of your input (MDY, YMD, YDM)

  For outputs, you use the CONVERT command with the STYLE option:

  SELECT CONVERT(VARCHAR(20), GETDATE(), 101)

  Refer to the BOL for more information on all of that.

  -SQLBill

  BOL=Books OnLine=Microsoft SQL Server's HELP

  Installed as part of the Client Tools

  Found at Start>Programs>Microsoft SQL Server>Books OnLine