date format yyyy/MM/dd

Question

In t-sql i'm using select getdate() function.
it is in yyyy/MM/dd format.

I'm retrieving that date into my UI.

but it is showing this format dd/MM/yyyy

the result is (Conversion failed when converting date and/or time from character string).

how can i change the date format to yyyy/MM/dd.

the coming output = " 27-05-2014 00:18:22 "
My required output = " 2014-05-27 00:18:22"

Thanks In Advance!

Jignesh Trivedi · Answer

hi, Are you pass your UI date to query? how? suppose you are writing query and execute it using command or adaper than try following... using (SqlCommand command = new SqlCommand( "SELECT * FROM table1 WHERE Date = @Date", connection)) { DateTime dt; DateTime dt; DateTime.TryParseExact("your date", "dd/MM/yyyy", new System.Globalization.CultureInfo("en-US"), System.Globalization.DateTimeStyles.None, out dt); // // Add new SqlParameter to the command. // command.Parameters.Add(new SqlParameter("Date", dt)); // // Read in the SELECT results. // SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { } } hope this will help you.

Khan Abrar Ahmed · Answer

Hi, you can try this also SELECT CONVERT(VARCHAR(24),GETDATE(),120) or please refer the link http://www.sqlusa.com/bestpractices/datetimeconversion/

Khan Abrar Ahmed · Answer

Hi, can you please let us know where exactly you want the out in sql db or in c#

vladimir omni · Answer

It's not working. Still having the same error.

Anupam Singh · Answer

Hi , You can format date in C# . You can find the list of formats : http://www.csharp-examples.net/string-format-datetime/ H ope it helps

Khan Abrar Ahmed · Answer

Hi you can try this in C# when you are fetching the date in the string C# Convert.ToDateTime(dateTime).ToString("yyyy-MM-dd"); or in sql server script SELECT REPLACE(CONVERT(VARCHAR(24),GETDATE(),102),'.','/')