CODEWITHSUNDEEP
sqlsql-serverdatetime
Irakli Lekishvili
Irakli Lekishvili

Reputation: 34158

Sql server date Column format

I have one column in SQL Server table with type date and its default setting is yyyy-MM-dd. I want to chage it like dd-MM-yyyy.

How can I do it?

Upvotes: 2

Views: 90874

Answers (7)

Olumercy
Olumercy

Reputation: 1

You can format it directly if you know the different date culture formats. For example English-USA.

SELECT FORMAT(column_name,'dd/MM/yyyy','en-us')

Upvotes: 0

tarun singh
tarun singh

Reputation: 1

if already have fill date in table column in other format and want to change it further

Select convert(varchar,Column_Name,113)Column_Name from Table_Name(nolock)

Upvotes: -1

user2671937
user2671937

Reputation: 11

I think you can use format() function for that as an alternative way.

Syntax:

SELECT FORMAT(column_name,format) FROM table_name;

enter code here (sample below)

SELECT ProductName, Price, FORMAT(Now(),'DD-MM-YYYY') AS PerDate FROM Products;

Upvotes: 1

bjorsig
bjorsig

Reputation: 1107

The database stores the data as a point in time. When you select from, for instance using Query Analyzer og SSMS, the format on screen will be dependent on the localization used on the server.

You should format the date in your application. You can use CONVERT(varchar(19), datecolumn, 105) where 105 is a code for particular format see SQL Server help for more codes. You need to understand that if you do that, you have changed the datatype of the column to varchar, that means you cannot use it for instance to do date calculations or other tasks specific for datetime.

I believe the best approach is to let the applications handle date formats and let the database simply store it. Most programming languages and reporting tool have much better ways to format dates than SQL Server.

Upvotes: 1

richard
richard

Reputation: 12498

You can format it for output like so:

SELECT convert(varchar, my_date_column, 105)

See here for different formats.

But what @Guffa says is true.

Upvotes: 2

Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

Are you looking for this ?

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

and see other format from here http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

Upvotes: 3

Guffa
Guffa

Reputation: 700342

its default setting is yyyy-MM-dd

No, it's not. There is no formatting information at all associated with the field.

The value is not formatted by the database, it's returned only as a point in time. Formatting that value into it's textual representation is done by the applcation that is getting the data from the database.

So, there is nothing that you can do in the database to change how the date value is formatted, you have to change that where the data is displayed.

Upvotes: 13

Related Questions