Results 1 to 5 of 5
  1. #1
    Robert2150 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Location
    Sparks, Nv
    Posts
    102

    Conversion of Date/Time field from Access to SQL

    I have recently migrated an Access Database to SQL 2016. I have a date field in two of my reports. The form of this field in Access is "Date/Time" format "Medium Date" In SQL the fields on the report show the time after the date, how can I just show date in the conversion?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    ALL dates are date/time. To show only date:
    format([datefld],"mm/dd/yyyy")

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I found a sheet that lists the data types for SQL Server 2012. Should be similar to SQLS 2016.
    https://www.connectionstrings.com/sq...pes-reference/

    There are 4 data types for dates:
    Date
    DateTime
    DateTime2
    SmallDateTime



    Maybe try setting your field type to "Date"??

  4. #4
    Robert2150 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Location
    Sparks, Nv
    Posts
    102
    The data types that you suggest are not available in Access. In Access the only choice for date in the Table Field is Date/Time, and in the "format" selection they are Short Date, Medium Date, and General Date and Time. There is a "Date" option in the SQL Server Manager, however it is not allowing me to change the format. I get the message "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created." Do you know any way around this issue?
    Thank you.

  5. #5
    Robert2150 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Location
    Sparks, Nv
    Posts
    102
    I solved the problem, by unchecking the "prevent saving changes that require table re-creation" box under OPTIONS. It worked just fine, and my date format has been changed.

    Thank you for all your interest.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-19-2016, 03:58 PM
  2. Type Conversion Failure of date field in Access
    By accessmatt in forum Queries
    Replies: 4
    Last Post: 05-01-2015, 03:39 PM
  3. Access Subquery Max Date/Time field
    By ruci1225 in forum Access
    Replies: 1
    Last Post: 04-08-2011, 07:33 AM
  4. Replies: 6
    Last Post: 01-04-2011, 05:43 PM
  5. Access VBA - date format conversion
    By benattal in forum Programming
    Replies: 3
    Last Post: 12-22-2008, 10:40 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums