SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Stripping a date from a varchar2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sojoo
Starting Member

3 Posts

Posted - 11/29/2006 :  15:49:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 11/29/2006 :  16:38:52  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/29/2006 :  16:44:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/29/2006 :  17:04:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is a forum where you post fully functional scripts!
Not to ask questions.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/29/2006 :  17:05:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And VARCHAR2, is that ORACLE datatype?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

sojoo
Starting Member

3 Posts

Posted - 11/29/2006 :  17:13:00  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/29/2006 :  17:17:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/29/2006 :  17:24:31  Show Profile  Reply with Quote
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
Go to Top of Page

sojoo
Starting Member

3 Posts

Posted - 11/29/2006 :  18:36:10  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/30/2006 :  02:18:49  Show Profile  Reply with Quote
dbforums might be a good place to try.

And don't mind MVJ!

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/30/2006 :  09:05:03  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/30/2006 :  10:19:53  Show Profile  Reply with Quote
I think it was the "subtle" bit that you let yourself down with!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 12/01/2006 :  02:59:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000