Microsoft SQL Server Management Studio Express - datetime

  • sql-532250

    Old Hand

    Points: 392

    How do I make Microsoft SQL Server Management Studio Express display Datetime variables in a table in the format of my choice ( when viewing those timedate variables on Studio Express)

    Currently they display as nn/dd/yy hh:mm:ss AM/PM

    I would much prefere to see them as yyyy/nn/dd HH:mm:ss

    ( I appreciate they are stored ion the database as Timedate (double integer) -- I am talking aboit how the application STudio Express choses for format the date & time when it displays the values in the tables)

  • Vidhya Sagar

    SSCrazy Eights

    Points: 8216

    [font="Courier New"]You can convert current date as you required by below piece of code

    select convert(varchar(25),getdate(),101)

    You can change the last variable value from 101 to 121 and each value represent one datetime format[/font]

  • Vidhya Sagar

    SSCrazy Eights

    Points: 8216

    [font="Courier New"]Refer the below link for more on the topic

    http://www.databasejournal.com/features/mssql/article.php/10894_2197931_1[/font]

  • sql-532250

    Old Hand

    Points: 392

    vidhya sagar (10/28/2007)


    [font="Courier New"]You can convert current date as you required by below piece of code

    select convert(varchar(25),getdate(),101)

    You can change the last variable value from 101 to 121 and each value represent one datetime format[/font]

    Thanks for the reply, but you may have missed the point of the question.

    How do I get "Microsoft SQL Server Manager Studio Express" to display "datetime" variables in the format of my choice.

    Ideally for any database and any table I use the application for.

    One would assume that there was a Preference settting somewhere to control how the application displays a "datetime" variable. ???

    However, if you have given me the answer, I must admit I am a SQL novice, and therefore do not know how to apply your answer to the "Studio Express" application. 🙂

  • priest Ivan Delic

    SSC Eights!

    Points: 832

    I will try to naswer to your question.

    You wrote:

    Thanks for the reply, but you may have missed the point of the question.

    How do I get "Microsoft SQL Server Manager Studio Express" to display "datetime" variables in the format of my choice.

    Ideally for any database and any table I use the application for.

    One would assume that there was a Preference settting somewhere to control how the application displays a "datetime" variable. ???

    Try this:

    1. Open Microsoft SQL Server Manager Studio Express, and connect to your SQL Server.

    2. Find New Query label on the top-left side of SQL Server Management Studio.

    Simply type:

    SELECT convert(varchar(25) getdate(), 101)

    and click EXECUTE button.

    This will make SQL Server to display your date/time format as you wish. You could change number 101 by others between 101 and 125.

    Hope that this will help

  • jensmander

    SSC Journeyman

    Points: 75

    Impressively, you STILL didn't get the point!

    The question is about how to CONFIGURE an application (i. e. MS SQL Server Management Studio) to display datetime values BY DEFAULT (i. e. without modiying the query by applying CONVERT etc.) in a certain format.

    Maybe you want another shot at it

  • Sigerson

    Hall of Fame

    Points: 3697

    I understood your question and AFAIK the answer is NO. Check for yourself under Tools-->Options... You'll find several options for formatting the results of a SQL query. Unfortunately, date formatting doesn't appear to be one of them.

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • Sigerson

    Hall of Fame

    Points: 3697

    Alternatively you could design your date fields as VARCHAR(10) and format them any way you wish. Of course that would completely negate the extreme usefulness of the DATETIME type and would be a very poor design choice. DATETIME and SMALLDATETIME exist as they are for very good reasons.

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • Joie Andrew

    One Orange Chip

    Points: 27271

    Currently they display as nn/dd/yy hh:mm:ss AM/PM

    I would much prefere to see them as yyyy/nn/dd HH:mm:ss

    I think by default these are coming from the locale settings on the system. So, if you do not want to specify the format of the datetime display when returning results I think the only way to do it will be to change the locale settings and specify how datetime will be represented by default.

    Joie Andrew
    "Since 1982"

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply