Sql server date Column format

I have one column in开发者_如何学Go 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?

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.

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/

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.

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.

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;

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')

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)