Change-Format Text to Date - SQL Server Forums

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

Change-Format Text to Date

Author

Topic

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-12 : 11:02:03

I have a column of text 010208 that is suppose to be a date. How can I change\format this column to read 01/02/2008?

Lisa Jefferson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 11:10:23

1 Always use proper datatype DATETIME to store dates
2 Format the dates in front end aplication (if used)
3 select stuff(stuff(col,5,0,'/'),3,0,'/') from your_table

Madhivanan

Failing to plan is Planning to fail

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-12 : 11:20:31

Did not work? Is stuff "convert"?

Lisa Jefferson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 11:23:27

quote:
Originally posted by l-jeff@excite.com

Did not work? Is stuff "convert"?

Lisa Jefferson

What do you mean by "Did not work?"?

Madhivanan

Failing to plan is Planning to fail

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-12 : 11:31:09

It does not like the information I replaced in your statement. I inserted convert for stuff. Is that right? The column is just plain numbers, not format. 060108.

Lisa Jefferson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 11:35:18

declare @d varchar(10)
set @d='010208'
set dateformat dmy
select convert(varchar(10),cast(stuff(stuff(@d,5,0,'/'),3,0,'/') as datetime),103)

Madhivanan

Failing to plan is Planning to fail

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-12 : 12:15:40

I ran that query. Works great. When I change the information to fit my data, I get: Subquery returned more than 1 value. What does that mean?

I changed set@d= column of date in my table

Lisa Jefferson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 12:22:14

set dateformat dmy
select convert(varchar(10),cast(stuff(stuff(column ,5,0,'/'),3,0,'/') as datetime),103) from your_table

Madhivanan

Failing to plan is Planning to fail

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-12 : 13:17:15

WORKS PERFECTLY!!!!!!!!!!!!!!!!!!!!!
Thanks so much.

Lisa Jefferson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 13:28:00

quote:
Originally posted by l-jeff@excite.com

WORKS PERFECTLY!!!!!!!!!!!!!!!!!!!!!
Thanks so much.

Lisa Jefferson

Well. Also consider points 1 and 2 in my first reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources