date format conversion

Indu Yadav

9y1.5k.NET

when i am saving date in database it save in "yyyy-mm-dd"

code is:

if(model.Date!=null)

{

string date = model.Date;

}

but i want to save it into "dd-mm-yyyy" format

how to do??

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Code Alone9y ago
SQL Server saves the value to datetime field in his defined manner. You can`t change it.
If you have datetime datatype, then there are no need to convert the date.
When you retrive the date then convert the date in string by
SELECT CONVERT(NVARCHAR(50),GETDATE(),114)

there are various formats strting from 100 to 114.

For your result try below :
SELECT CONVERT(NVARCHAR(50),GETDATE(),105)

**You wrote mm, while all answers for MM.
+2
Vijay Kumar9y ago
Don't insert a date as a specific format. Dates should be stored using the appropriate Date/DateTime types, and they should be stored as a universal format. It's a common misconception that you should be storing dates in a particular format - this is a bad practice to get into. What date was 10/11/2013? Was it the 10th November 2013 or the 11th October 2013? By storing the date in a universal format, you remove ambiguity and you then do the date formatting work at the place it should be done - at the UI after you have retrieved it, where it can be made to follow local date/time conventions.
0
Karthi Keyan9y ago
@Indu,

In database date datatype store the date in "yyyy-MM-dd". you can't change that. but you can the datatype. change your date Datatype into varchar(). then Pass the Date value as a string in your format.

To convert date into string use this code.
string date = DateTime.now.Date.ToString("dd-MM-yyyy");
0
Robert Raboud9y ago
Datetime values are not stored a format of yyyy-mm-dd or mm-dd-yyyy or any such sillyness.
So how does SQL Server internally store the dates? It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers. For the date portion, the value SQL Server stores is the number of days before or after a base date of January 1, 1900. Because of this storage protocol, SQL Server assumed the date of January 1, 1900, when I didn't supply the date in my first example. SQL Server internally stored a value of 0. A negative number represents a date earlier than January 1, 1900.
SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms). You can see the values for days and clock ticks by converting a datetime value to a binary(8) value and using the substring function to extract each set of 4 bytes.

DONT DONT DONT mistake the way it displays data with how the data is stored.
+1
Nilesh Shah9y ago
when you save date to DB it takes the format as YYYY-MM-DD to remove the confusion of first month comes or date eg. India follows DD/MM format, while US follows MM/DD format.

so let SQL store the format in that format, while showing the result on UI you should format is according to user's UI culture
+2
Amit Gupta9y ago
Hey

Firstly, if you are saving a Date to database then DONOT convert it to string (as it loose its functionality)

Avoid using the dd-mm-yyyy format as thia is only fpr display purpose according to the language culture.

Use only the global format like yyyy-MM-dd format, as if you use 05-08-2017 then system cannot determine whether it was 5th Aug or 8th May. This will leads to greater confusion. So avoid string transformation and pass the Date Datatype

Moreover, if needed to display in any date format you can visit the below link
https://www.dotnetperls.com/datetime-format

Good Luck !!
0
Robert Raboud9y ago
i would save it to the DB as a DateTime, ie make the column type DateTime.

then make the model type DateTime and you can format it to a string anyway you want.
0
Catcher Wong9y ago
Hello ,
Maybe the following things you need to take into consideration!
First of all , if you use a 'String' type(such as varchar,nvarchar..) to store you data in your database , you need to format the data before inserting into the table.
For example , you can use the method ToString to do that:
string date1 = DateTime.Now.Date.ToString("yyyy-MM-dd");
string date2 = DateTime.Now.Date.ToString("dd-MM-yyyy");
However , if you use a 'Datatime' type(such as datatime,date..) to store you date , the database will show you a default format of date ,such as 'yyyy-MM-dd',because you do not specify the formatting ! In this scenario , you only make some changes to your SQL , specifing the formatting which you want !

For example ,you can use to_char(ORACLE) to do that
select to_char(sysdate,'yyyy-mm-dd') from dual ;
0
Rafnas T P9y ago
try as follows

model.Date.ToString("dd-mm-yyyy");
0
Manish Kumar9y ago
Which database you are using. If Sql server then sql server not save data in dd-mm-yyyy .Either you have to change data type of date column or try below query for displaying date in dd-mm-yyyy format.
declare @date datetime
set @date=GETDATE()
select convert(varchar(20),@date,105) as date

Thanks,
+1

date format conversion

when i am saving date in database it save in "yyyy-mm-dd"

code is:

if(model.Date!=null)

{

string date = model.Date;

}

but i want to save it into "dd-mm-yyyy" format

how to do??

Datetime values are not stored a format of yyyy-mm-dd or mm-dd-yyyy or any such sillyness.

So how does SQL Server internally store the dates? It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers. For the date portion, the value SQL Server stores is the number of days before or after a base date of January 1, 1900. Because of this storage protocol, SQL Server assumed the date of January 1, 1900, when I didn't supply the date in my first example. SQL Server internally stored a value of 0. A negative number represents a date earlier than January 1, 1900.

SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms). You can see the values for days and clock ticks by converting a datetime value to a binary(8) value and using the substring function to extract each set of 4 bytes.

DONT DONT DONT mistake the way it displays data with how the data is stored.