Results 1 to 4 of 4
  1. #1
    Pbear88 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    May 2017
    Posts
    26

    Changing the default system date format in SQL Server Management Studio.

    I've created a MS Access front end with a SQL Server backend. I've converted and linked the Tables I needed, but I'm having trouble with the default display of my date fields. Looks like the default format for SQL Tables is yyyy-mm-dd. I would like it to be mm/dd/yyyy. I've tried different date types (datetime, date, shortdatetime), but they all have the yyyy-mm-dd format. I've read about the Convert function, but does that permanently change the display on the SQL Table, or just in that query view? If there is not a way to change this default format, can I display it the way I prefer on the Access side (on the forms)?


    Thank you for any feedback you can provide.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Nothing to worry about it. (You should not be working directly with tables).
    When you type a date into Access, your entry is stored as a number, where the integer part represents the date and the fraction part the time (part of a day).

    When you type a date into SQL Server, internally dates are stored as 2 integers. (see http://blogs.lessthandot.com/index.p...in-sql-server/)
    The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second.

    SQL Server controls what dates look like when looking at raw data (as in a table). Don't confuse how date/time is stored and how it is displayed. If you want a different display format, use the format function. My dates in SQL Server Express are YYY-MM-DD when looking at a table; in Access in forms and reports, they are displayed as MM-DD_YYYY.


    Quote Originally Posted by Pbear88 View Post
    can I display it the way I prefer on the Access side (on the forms)?
    Yes. I have no problems with the way Access displays dates. After I converted the Access tables to SQLSE, I did not have to do any changes to Access forms - displayed the dates as usual.

  3. #3
    Pbear88 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    May 2017
    Posts
    26
    Thanks for your reply. Can you expand on the format function? Is this format function applied to the Access forms and report fields? I keep getting the YYYY-MM-DD format from SQL displaying in my forms.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Look in Access Help for the format function syntax.


    I just checked - I have a table in SQLSE that has dates. I open the table in Management studio and the dates are in YYYY-MM-DD HH:MM:SS format.
    I open a form based on the above query and the dates are in MM-DD-YYYY format.
    I open a query based on that linked table and the dates are in MM-DD-YYYY format.
    I open the same (linked) table in Access and the dates are in MM-DD-YYYY format.


    I was using A2K a couple of years ago, trying to get familiar with SQL Server (same FE, tables in SQL Server Express 2000), but I don't remember any problems with dates being displayed in international format.

    I'm stumped......


    Maybe post your dB with a couple of example records???? (FE and the SQL BE)

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

Similar Threads

  1. IntelliSense is not working in Management Studio 2012
    By adnancanada in forum SQL Server
    Replies: 0
    Last Post: 09-29-2016, 08:58 AM
  2. IntelliSense (SQL Server Management Studio)
    By adnancanada in forum Queries
    Replies: 0
    Last Post: 08-04-2016, 11:42 PM
  3. Dance Studio Management Database
    By advomystics in forum Database Design
    Replies: 5
    Last Post: 01-28-2016, 11:10 PM
  4. Replies: 1
    Last Post: 06-19-2015, 08:42 AM
  5. Link the data from SQL server management studio to xls.
    By krkow in forum Import/Export Data
    Replies: 1
    Last Post: 12-12-2011, 08:30 AM

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