Using DateTime2 in Access and SQLserver

• chris-939265

  SSC Enthusiast

  Points: 168

  More actions

  April 14, 2020 at 7:58 am

  #3742626

  Hi,

  I last used Access/SQlserver about five years ago and have forgotten more than I learnt <grin>.

  I have created a SQLserver table for the import of data from audit logs.  I only have screen-access to the tabulated logs and cut&paste from the screen to Excel which provides columns of text.  I have created the SQL table manually using nvarchar(255) for each field - adding a primary_key field on the front.  There are two date/time text fields - an example being "Apr 13, 2020, 11:02:11 AM".  I would like to be able to sort by date/time and so have added two datetime2 fields.  I then use SMSS to import the logs a day or two at a time ( a few thousand entries a day).  I use Access as a front end - the tables being linked.

  My question is - how do I fill the datetime2 fields?  Using an Access query gives a type missmatch (datevalue()).

  many thanks - Chris

• chris-939265

  SSC Enthusiast

  Points: 168

  More actions

  April 14, 2020 at 8:11 am

  #3742633

  Hi - Access 16,  SMSS 18.5, SQLserver 2019 running on Windows 10.

• pietlinden

  SSC Guru

  Points: 63546

  More actions

  April 14, 2020 at 8:38 am

  #3742637

  NVARCHAR()??? You sure you need that?

  Check this out: https://support.microsoft.com/en-us/office/comparing-access-and-sql-server-data-types-9188f41d-6c0e-4733-9d20-d08916f50bd2?ui=en-us&rs=en-us&ad=us

  Access Date/Time data type maps to SQL Server Date/Time data type, not DateTime2.