Stripping a date from a varchar2

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

Script Library

Stripping a date from a varchar2

Author

Topic

sojoo
Starting Member

3 Posts

Posted - 2006-11-29 : 15:49:35

Hi All,

I'm having trouble coming up with a function that will allow me to display only the date and time from a string in the following format:

JSMITH 1/1/2006 1:00:00AM

I've tried using substr with a negative position value, but since the date and time for each instance can be different, with it being anywhere from 18-21 characters, if the length is anything less than 20-21 characters, it will return part of the end of the username. What function can I use to only retrieve the date and time?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-29 : 16:38:52

can you take everything after the first space as the datetime? or is this possible: JSMITH JR 1/1/2006 1:00:00AM

if that nasty string isn't possible, then you could just use charindex() to find the first space, and substring() to take the remainder.

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 16:44:16

-- prepare test data
declare @info varchar(100)

select @info = 'JSMITH 1/1/2006 1:00:00AM'

-- do the work
select right(@info, charindex(' ', reverse(@info), 15) - 1)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 17:04:48

This is a forum where you post fully functional scripts!
Not to ask questions.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 17:05:40

And VARCHAR2, is that ORACLE datatype?

Peter Larsson
Helsingborg, Sweden

sojoo
Starting Member

3 Posts

Posted - 2006-11-29 : 17:13:00

I'm sorry for posting in the wrong section, but I did not see the one for questions. The database is in Oracle, but I'm using CR 8.5 to modify a current report.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 17:17:19

Yes, this is a SQL Server forum.
However, I believe my query above will work even in ORACLE. At least the logic behind it will.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-29 : 17:24:31

quote:
Originally posted by sojoo

I'm sorry for posting in the wrong section, but I did not see the one for questions. The database is in Oracle, but I'm using CR 8.5 to modify a current report.

There is no section on this site for Oracle questions. This site is for Microsoft SQL Server questions.

Perhaps you did not see the notice that appears in the top right hand corner of every page that says, "Microsoft SQL Server articles, news and forums" and did not see that this forum is under the heading "SQL Server Forums".

CODO ERGO SUM

sojoo
Starting Member

3 Posts

Posted - 2006-11-29 : 18:36:10

Thanks for the help Peter and Jezemine. I really appreciate it. =)

And i'm sorry that I accidentally posted here with my Oracle question Mr. Jones. I'll be sure to not post my apparently lowly and offensive questions on here again.

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 02:18:49

dbforums might be a good place to try.

And don't mind MVJ!

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-30 : 09:05:03

quote:
Originally posted by Kristen

dbforums might be a good place to try.

And don't mind MVJ!

Kristen

Guess my subtle irony didn’t go over well.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 10:19:53

I think it was the "subtle" bit that you let yourself down with!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-01 : 02:59:35

www.DBForums.com
www.ORAFAQ.com

1 What is the use of using nvarchar2 or varchar2 column when there is datatype DATE which is specifically used to store dates?
2 Why did you concatenate name with date?
You need Normalization

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources