views:

1955

answers:

3

hi guys, I have date in mm/dd/yyyy format in database.I want to display in form as dd/mm/yyyy. Can anybody help?I want to get time along with date.

+2  A: 

CONVERT(VARCHAR(10), YourField, 103)

Per your comment - you want the time as well.


select (CONVERT(VARCHAR(10), YourField, 103) + ' ' + CONVERT(VARCHAR(15), YourField, 108)) as DateTime

http://msdn.microsoft.com/en-us/library/aa226054.aspx

Jeff Olson
I want to get time along with date.Now only date is coming
now the result is 10/08/1429 9:28:55:000PM means format as my desired but year is not in correct format
Why the year is 1429
2008 is showing a 1429
Sorry - that was a "Hijri" specific format I referenced. Try the edited code sample.
Jeff Olson
+1  A: 

A date value doesn't have a format at all. It gets it's format when you convert it to a string.

You can use the convert function to convert the value in the database, but you should rather leave that to the code in the user interface.

Guffa
I want to get time along with date.Now only date is coming
A datetime value always has a time component. Are you using convert? How are you displaying the value?
Guffa
A: 

What Guffa said, plus this from books online: http://msdn.microsoft.com/en-us/library/aa226054.aspx

CONVERT(VARCHAR(10), column, 103)

103 is yyyy
3 is yy

Brettski