Extracting the middle of a string

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

SQL Server 2005 Forums

Transact-SQL (2005)

Extracting the middle of a string

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-08-19 : 11:35:56

Hello,

I have a date of birth field that is set up like this 20080819 instead of 8/19/2008. It was not my choice its a product we purchased from another company that doesn't have to use it to create reports.... :). Anyways in Access I always use this statement to format it to look the way I want it to look

mid([a.date_of_birth],5,2) & '/' & Right([a.date_of_birth],2) & '/' & Left([a.date_of_birth],4)

but in SQL it does not recognize MID. Does anyone know what the correct term should be to extract data from the middle of a string?

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 11:44:49

try with CONVERT(datetime,yourstring,112)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 11:46:26

SUBSTRING.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 11:47:23

Visakh, what if the DOB field is INT?

SELECT CONVERT(CHAR(10), STR(DOB, 8), 101)

E 12°55'05.25"
N 56°04'39.16"

sross81
Posting Yak Master

228 Posts

Posted - 2008-08-19 : 11:47:43

Convert works the same as Cast(date_of_birth as datetime). It does put it as date time but lists it as 2008-08-19 so still a little off from what I want as 8-19-2008. I decided to use an outside program to convert it to the format I want it. As long as its a datetime all of the programs like excel or crystal will let you set it out however you want. Thanks for the ideas! :)

quote:
Originally posted by visakh16

try with CONVERT(datetime,yourstring,112)

Thanks in Advance!
Sherri

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-19 : 12:27:38

Luckily the date format

20080819

is already one of the two ISO location independent date formats So you don't have to worry about that at least!

Choose one of the two depending whether it's stored as an INT or a CHAR


DECLARE @testAsString CHAR(8)
SET @testAsString = '20080819'

DECLARE @testAsINT INT
SET @testAsInt = 20080819

-- Storage type CHAR
SELECT CONVERT(CHAR(10), CAST(@testAsString AS DATETIME), 101)

-- Storage type INT
SELECT CONVERT(CHAR(10), CAST(STR(@testAsInt, 8) AS DATETIME), 101)

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-20 : 04:12:17

quote:
Originally posted by sross81

Convert works the same as Cast(date_of_birth as datetime). It does put it as date time but lists it as 2008-08-19 so still a little off from what I want as 8-19-2008. I decided to use an outside program to convert it to the format I want it. As long as its a datetime all of the programs like excel or crystal will let you set it out however you want. Thanks for the ideas! :)

quote:
Originally posted by visakh16

try with CONVERT(datetime,yourstring,112)

Thanks in Advance!
Sherri

You have taken a right decision

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources