change date format in sql

Question

if my date format is 2000-02-25 in my sql table . i wanted to change into 25-Feb-2000 . how can i change and what is the command have to user?

Answer 1

Maybe some of database tools allow various displayed formats of the date columns, but this feature does not seem available in Management Studio.

You can specify the format of the dates in your statements using CONVERT and FORMAT. For example:

select convert(varchar(max), DateColumn,  13), 
       format(DateColumn, 'dd-MMM-yyyy')
from MyTable

But you cannot change the internal representation of the data.

Answer 2

Do not confuse the "display" of a date with the "storage" of the date.

The display of the date is up to the application. You can change the date to a string and force a specific display by using several methods, including FORMAT().

See:
https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15

Answer 3

What is the data type of the column?

If the type is date, there is no format. What is stored in this case, I believe, is a three-byte integer with the number of days since 0001-01-01. (Assuming a Gregorian calendar all the way.)

If you want to format the date in some way, this is best done in the client, and according to the client's regional settings.

If the date is stored as a string, you will need to do some acrobatics, but you should never store date/time values as strings. This is an accident waiting to happen and sooner or later you are looking at 2023-02-29.

Answer 4

Hi，@Nikethan Antony

Welcome to Microsoft T-SQL Q&A Forum!

Here I provide a combination of convert method and replace, you may be able to learn more about date format conversion.Of course, as violel said, if use SQL Server 2012+ or later,
you can use FORMAT(value, format [, culture ]).

select CONVERT(VARCHAR(11),REPLACE(CONVERT(VARCHAR(11),datetime, 106), ' ', '-'))  
from #t1  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.