Update datetime format

Question

1.00/5 (1 vote)

See more:

Hello,

I have some records at this format "25-6-2013" for example

I want to update all records to the format "06-25-2013"

the column data type is datetime

is there any way to do that ?

thanks in advance

Posted 26-Aug-13 6:04am

Hamza M. S. Abazeed

Add a Solution

Comments

Boipelo 26-Aug-13 12:38pm

Why you want to convert them? Why not display using format "mm-dd-yyy". I know you said they are datetime type but I doubt it, datetime type will inclued the time. Datetime = date + time.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard C Bishop · Answer 1 · 2013-08-26T06:06:00

Solution 1

Yes, just search for date-time conversion formats.

Click me![^]

me too![^]

Posted 26-Aug-13 6:06am

Richard C Bishop

Comments

Thanks7872 26-Aug-13 12:21pm

Good one. Upvoted..!

Richard C Bishop 26-Aug-13 12:31pm

Thank you kindly!

Hamza M. S. Abazeed 26-Aug-13 12:27pm

Thanks richcb

I tried to use them

but when I excute:

Select Convert(datetime, '25-6-2013', 103) // or 101 or any

I get the result : 25-6-2013

I want the result to be 6-25-2013

Richard C Bishop 26-Aug-13 12:31pm

I think you want to use 105.

Hamza M. S. Abazeed 26-Aug-13 12:29pm

or this message as result

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Hamza M. S. Abazeed 26-Aug-13 12:39pm

105 not work too :|

Richard C Bishop 26-Aug-13 12:41pm

Can you use the "Improve question" widget to add the code you are using please?

Mike Meinz · Answer 2 · 2013-08-26T06:47:00

You do not have to change the data in the database.

If the database column data type is DateTime, then the dates are stored in the DateTime format not in a string format. It is the proper thing to do to store dates in a DateTime format. Month-first or day-first is a display choice not a choice you have on how the date is stored. The DateTime data type stores dates in an internal format that allows the value to be formatted in many different ways after it is retrieved from the database.

When the dates are retrieved into your C# program as a DateTime variable, they can be formatted in many different ways by your C# application. Here are some examples:

C#

DateTime dtVar;
//
// Retrieve the date from database and put it into dtVar 
// then
Console.WriteLine(dtVar.ToString("yyyy-MM-dd HH:mm:ss"));
Console.WriteLine(dtVar.ToString("MM-dd-yyyy"));
Console.WriteLine(dtVar.ToString("MM/dd/yyyy"));
Console.WriteLine(dtVar.ToString("dd-MM-yyyy"));
Console.WriteLine(dtVar.ToString("dd/MM/yyyy"));

If you want to return the date from SQL as a string (which I do not recommend), then you can use the Convert function. Here is an example of converting the same date into four different strings:

SQL

select convert(varchar(10),reported_date,103), convert(varchar(10),reported_date,101),convert(varchar(10),reported_date,105),convert(varchar(10),reported_date,110)    from inventory_workorders

Results:

13/07/1992	07/13/1992	13-07-1992	07-13-1992
07/02/1995	02/07/1995	07-02-1995	02-07-1995
20/07/1993	07/20/1993	20-07-1993	07-20-1993

Note that the values stored in the DateTime column in the database are not changed. The date is formatted from a DateTime data type to a string format using the format string in C# or the Convert function in SQL.