convert

Hi,
I need to change the format of a date column in one of the tables to reflect
a date that looks like mm/dd/yyyy. I used the following syntax that I know
it's wrong -it works in a select statement-:
alter table tbl_Reservation
convert(char(20),[from date],101) as [From Date]
Is there anyway to change the format on the table itself?
TSWhat datatype do you wish to have for that column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
> Hi,
> I need to change the format of a date column in one of the tables to refle
ct
> a date that looks like mm/dd/yyyy. I used the following syntax that I know
> it's wrong -it works in a select statement-:
> alter table tbl_Reservation
> convert(char(20),[from date],101) as [From Date]
> Is there anyway to change the format on the table itself?
>
> --
> TS|||I just need to change the format of that column from for example 2005-05-16
00:00:00 to a format of mm/dd/yyyy
TS
"Tibor Karaszi" wrote:

> What datatype do you wish to have for that column?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
>|||Tibor asked the data type of the field because if it's a datetime then
there's no way to "change the format" -- datetimes aren't stored in a
particular format, that's a display issue. If it's stored as a character typ
e
(which would probably be less than optimal), we would probably be able to
recommend some options.
"TS" wrote:
> I just need to change the format of that column from for example 2005-05-1
6
> 00:00:00 to a format of mm/dd/yyyy
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format"
Exactly. :-)
For more information, TS, I suggest you check out
http://www.karaszi.com/SQLServer/info_datetime.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KH" <KH@.discussions.microsoft.com> wrote in message
news:E84225DE-A465-40C2-A343-246FF6551D42@.microsoft.com...
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Yes, the data type of the field is datetime. In the front-end, it appears in
the format mm/dd/yyyy which is the way I want it even though in the query I
ran from SQL it appears in the datetime format that there is no way to
change. I didn't know this is the case!!
Thanks a lot for your help.
--
TS
"KH" wrote:
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Hi,
You cant change the storage format if you use datetime data type. Only way
is during extraction you could use CONVERT function to format the date.
Alternatevely you could use the VARCHAR datatype to store the date format as
you need. Wile inserting you could format the field with CONVERT function.
But I recommend you to use datetime data type and while extraction you can
format it using CONVERT function.
Thanks
Hari
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:FDF45283-3516-46A5-92FA-8AE1EF27AAE1@.microsoft.com...
> Yes, the data type of the field is datetime. In the front-end, it appears
> in
> the format mm/dd/yyyy which is the way I want it even though in the query
> I
> ran from SQL it appears in the datetime format that there is no way to
> change. I didn't know this is the case!!
> Thanks a lot for your help.
> --
> TS
>
> "KH" wrote:
>