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
|||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
