Microsoft SQL Server Management Studio Express - datetime

Question

Post reply

Microsoft SQL Server Management Studio Express - datetime

sql-532250

Old Hand

Points: 392
More actions
October 28, 2007 at 11:14 am

#176609

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)

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

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

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

[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]

Regards..Vidhya Sagar
SQL-Articles

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 2

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

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

Regards..Vidhya Sagar
SQL-Articles

sql-532250 Old Hand Points: 392 More actions · Answer 3

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: 942 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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: 27360 More actions · Answer 8

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"