convert date time from mm/dd/yyyy as.....dd/MM/yyyy

Question

1.00/5 (2 votes)

See more:

How to convert date in sqlserver 2005 using query.
==================================================

I have a date and time in database as 10/16/2012 03:07:42 PM it dsiplays as mm/dd/yyyy.

am reading this time using reader.

C#

SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "Select Lupdate from TimeLastUpdate";

        using (con)
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
               lblUpdate.Text = reader["Lupdate "].ToString();

              lblUpdate.ForeColor = Color.Red;
              lblUpdate.Visible = true;
            }

Now I need to convert it in dd/MM/yy 03:07:42 PM format.

So, I need Sql Query for Converting Date time as dd/MM/yyyy 03:07:42

Please help me friends,

am trying a lot .

we need to change here i think so

cmd.CommandText = "Select Lupdate from TimeLastUpdate";

Thanks.

Posted 15-Oct-12 23:55pm

Software Engineer 892

Updated 15-Oct-12 23:59pm

Sushil Mate

v2

Add a Solution

Comments

D-Kishore 16-Oct-12 6:20am

What is the datatype you are using for Lupdate column in sql sever table.

AshishChaudha 16-Oct-12 6:29am

same question http://www.codeproject.com/Answers/476614/Howplustoplusdisplayplusdateplusmonthplusyearplusi#answer2

Dont ask same question again and again.

8 solutions

Solution 2

Hi,

Use below code

SQL

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

                                        – Oct  2 2008 11:01AM

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                        – Oct  2 2008 11:02:44:013AM

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

                                        – 02 Oct 2008 11:02:07:577

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

                                        – 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy

Posted 16-Oct-12 0:01am

Shanalal Kasim

Solution 8

C#

Using Globalization;
 
CultureInfo ci=new CultureInf("gu-IN");


SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "Select Lupdate from TimeLastUpdate";
 
        using (con)
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
 
            if (reader.Read())
            {                
              lblUpdate.Text = DateTime.ParseExact(reader["Lupdate "].ToString(),ci);
              lblUpdate.ForeColor = Color.Red;
              lblUpdate.Visible = true;
            }

Thanks

Posted 16-Oct-12 0:28am

AshishChaudha

Solution 1

use this:

DD/MM/YYYY HH:MI:SS:MMMAM SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM

Posted 15-Oct-12 23:58pm

Mukesh_Chauhan

Solution 3

Change your SQL query from

SQL

cmd.CommandText = "Select Lupdate from TimeLastUpdate";

To

SQL

cmd.CommandText = "Select CONVERT(VARCHAR(20) ,Lupdate ,131) from TimeLastUpdate";

Check more details of this CONVERT function at

http://www.w3schools.com/sql/func_convert.asp[^]

Hope that helps. If it does, mark answer as solution and/or upvote.

Thanks
Milind

Posted 16-Oct-12 0:01am

MT_

Solution 4

It depends on how you are storing it in the database: if you are storing it as a DateTime column, then it is already stroed as you need, you just need to format it appropriately for display. If you are storing it as a string, then change your database design, and store it as a DateTime format - it is much more flexible and compact.

Once you have it as a DateTime, to format it for display, you just read it as a DateTime:

C#

DateTime dt  = (DateTime) reader["Lupdate"];
string myFormat = dt.ToString("G");

Will probably do it as you want, but have a look here: Formatting a DateTime for display - format string description[^]

Posted 16-Oct-12 0:01am

OriginalGriff

Solution 5

Instead of adding it in SQL, you can convert it to required format from C# code as below:

System.Convert.ToDateTime(strDateTime).ToString("dd/MM/yy hh:mm:ss tt, CultureInfo.InvariantCulture)

In your case:

lblUpdate.Text = System.Convert.ToDateTime(reader["Lupdate "]).ToString("dd/MM/yy hh:mm:ss tt, CultureInfo.InvariantCulture);

Posted 16-Oct-12 0:08am

menarenprvn

Updated 16-Oct-12 0:11am

v2

Solution 6

C#

SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "Select CONVERT(VARCHAR(20), Lupdate , 131) from TimeLastUpdate";
 
        using (con)
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
 
            if (reader.Read())
            {
               lblUpdate.Text = reader["Lupdate "].ToString();
 
              lblUpdate.ForeColor = Color.Red;
              lblUpdate.Visible = true;
            }

Posted 16-Oct-12 0:09am

Sushil Mate

Updated 16-Oct-12 0:10am

v2

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)

vivektiwari97701 · Accepted Answer · 2012-10-16T00:13:00

Solution 7

SQL

lblUpdate.Text = DateTime.Parse(reader["Lupdate "].ToString() ).ToString("dd/MM/yyyy hh:mm"));

Posted 16-Oct-12 0:13am

vivektiwari97701

Comments

Software Engineer 892 16-Oct-12 6:59am

@ vivektiwari97701 your solution is good it is correct what i want, but it is not displaying am or pm

it is displaying as 16/10/2012 04:07

I need it as 16/10/2012 04:07:42 pm

Please help.

Thanks for EVERRRRRR.

vivektiwari97701 16-Oct-12 7:08am

just write "dd/MM/yyyy hh:mm tt"

Software Engineer 892 16-Oct-12 7:30am

Thank you very Much @ vivek tiwari.