Date format in Convert()

I'm trying to get smalldate from the date field in Sql table.
I want the format mm/dd/yyyy ( american format)

The query goes like this:
CONVERT([SmallDateTime],StatusDate,101)

still the result is:
2006-09-28 00:00:00

Krutika wrote:

I'm trying to get smalldate from the date field in Sql table.
I want the format mm/dd/yyyy ( american format)

The query goes like this:
CONVERT([SmallDateTime],StatusDate,101)

still the result is:
2006-09-28 00:00:00

convert(varchar,StatusDate,101)

It makes no sense to convert a datetime field to smalldatetime in a format unsupported. The result you've obtained is how the data is stored in a smalldatetime field. Can't do it any other way unless converting to varchar (or another character type)|||This works but I also want to retain the DateTime data type.

How do I do it in a query?|||

CONVERT(char(10),StatusDate,101)

hth

|||

Krutika wrote:

This works but I also want to retain the DateTime data type.

How do I do it in a query?

You can't... You can't store a mm/dd/yyyy format in a datatime/smalldatetime field. You can display it in the format you want with the example I gave, though.|||http://msdn2.microsoft.com/en-us/library/ms187819.aspx|||

here are all the formats:

Date Time in SQL SERVER

To get only the date

select convert(varchar, getdate(), 101)

12/15/2006

select convert(varchar, getdate(), 102)

2006.12.15

select convert(varchar, getdate(), 103)

15/12/2006

select convert(varchar, getdate(), 104)

15.12.2006

select convert(varchar, getdate(), 105)

15-12-2006

select convert(varchar, getdate(), 106)

15 Dec 2006

select convert(varchar, getdate(), 107)

Dec 15, 2006

select convert(varchar, getdate(), 110)

12-15-2006

To Get only the time :

select convert(varchar, getdate(), 108)

16:37:05

select convert(varchar, getdate(), 114)

16:37:05:120

To get Both date and time

select convert(varchar, getdate(), 100)

Dec 15 20064:38PM

select convert(varchar, getdate(), 109)

Dec 15 20064:38:41:197PM

select convert(varchar, getdate(), 112) --ANSI un-separated date format ->RECOMMENDED

20061215

select convert(varchar, getdate(), 113)

15 Dec 2006 16:41:11:937

|||http://www.sql-server-helper.com/tips/date-formats.aspx