extract (text to column) - 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

extract (text to column)

Author

Topic

mastertx
Starting Member

1 Post

Posted - 2008-04-30 : 21:00:00

I have a column named DATEID in a table DATA that contains the
following format connected to each other(date and name):

20071030Jones
20071020Smith
20070918Rogers
20080122Williams
etc..

What would the syntax be to insert a column to the right of this one,
and extract the first 8 digits from the data in the DATEID column and
insert that into the new column DATE, therefore making it easier for
me to query against an actual date?

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-30 : 21:18:42

[code]

select
Date = convert(datetime,left(a.DATEID,8))
from
(
-- Test Data
select DATEID = '20071030Jones' union all
select DATEID = '20071020Smith' union all
select DATEID = '20070918Rogers' union all
select DATEID = '20080122Williams'
) a

Reply:
Date
---------------------------
2007-10-30 00:00:00.000
2007-10-20 00:00:00.000
2007-09-18 00:00:00.000
2008-01-22 00:00:00.000

(4 row(s) affected)[/code]

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-02 : 05:42:41

quote:

Originally posted by Michael Valentine Jones



select
	Date = convert(datetime,left(a.DATEID,8))
from
	(
	-- Test Data
	select DATEID = '20071030Jones'		union all
	select DATEID = '20071020Smith'		union all
	select DATEID = '20070918Rogers'	union all
	select DATEID = '20080122Williams'
	) a



Reply:
Date
---------------------------
2007-10-30 00:00:00.000
2007-10-20 00:00:00.000
2007-09-18 00:00:00.000
2008-01-22 00:00:00.000

(4 row(s) affected)

CODO ERGO SUM

To handle illegal dates, you may need to add

where len(a.DATEID)=8 and isdate(a.DATEID)=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources