Converting date format in sqlserver

Question

CREATE TABLE Allvouchers (
    s_no INT IDENTITY(1,1) PRIMARY KEY,
    date_column DATETIME NOT NULL,
	voucher_Particulars VARCHAR(51) NOT NULL ,
    voucher_type VARCHAR(51) NOT NULL ,
    voucher_number VARCHAR(51)  NULL,
    party_name VARCHAR(51) NOT NULL,
    amount MONEY NOT NULL
);

the above are details of my sql server table . the date column is storing in the format of yyyy-mm-dd.

i want that it should store the data in the format of dd-mm-yyyy . is there a function for it

Answer 1

Hi @vijay kumar

i want that it should store the data in the format of dd-mm-yyyy

The date is a binary field (stores binary value regardless of formatting). When SQL store date, it is not using any format at all. So, you do not store date in SQL by any format - only displays based on the local set of your server.

If you want to show dd/mm/yyyy, use the Format method or Convert / Cast on the Date field to change the format.

It is not suggested to store date in a particular varchar format. For example, what date was 11-6-2023? Was it the 6th of November 2023 or the 11th of June 2023?

Best regards,

Cosmog Hong

---

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.

Answer 2

You can try convert(varchar, getdate(), 105)

Answer 3

Unfortunately it's not the best practice, but this should work:

CREATE TABLE YourTableName
(
    ID INT PRIMARY KEY,
    YourDateColumn VARCHAR(10) -- Assuming "dd-mm-yyyy" format
        CHECK (YourDateColumn LIKE '[0-3][0-9]-[0-1][0-9]-[0-9][0-9][0-9][0-9]'), -- Check the format
    OtherColumns INT, -- Add other columns as needed
);

Answer 4

in a Datetime column the date is not stored in a string format, its binary. when its converted to string, a format is required. the default is ISO, but you can specify the format. see docs:

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

select format(date_column, 'd', 'en-gb') as formatted_date
from Allvouchers