converting the date format in varchar column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

converting the date format in varchar column

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2011-11-02 : 15:06:31

I have a varchar 50 column in my table. I have the date in this format in that table

05 MAY 03 ,. I want to convert it to 2003-05-05 17:31:00

to yyyy-mm-dd HH:MM:SS
format, but when I do this,

update MSG_NEW
set initial_date = CONVERT(varchar(20), initial_date, 121)

I still get 05 MAY 03.

How can i convert it to yyyy-mm-dd HH:MM:SS

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-11-02 : 15:24:18

You should store date values as Datetime or smalldatetime datatype - not varchar. Then you can present the dates in any format you want. As datetime lots of (date) functions are available to you including sorting.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-11-02 : 15:26:58

But to answer your question you need to convert THROUGH datetime first:
select convert(varchar(23), convert(datetime, '05 MAY 03'), 121)

But please don't store the date as varchar - store it as Datetime.

Be One with the Optimizer
TG

anjali5
Posting Yak Master

121 Posts

Posted - 2011-11-02 : 16:51:58

Thanks!!

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-03 : 04:28:01

hi you can update your table using below update statement

update MSG_NEW
set initial_date = CONVERT(varchar(23), convert(datetime, initial_date), 121)

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources