| Author |
 Topic  |
|
|
sojoo
Starting Member
3 Posts |
 Posted - 11/29/2006 : 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
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 11/29/2006 : 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
Sweden
29138 Posts |
Posted - 11/29/2006 : 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
Sweden
29138 Posts |
Posted - 11/29/2006 : 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
Sweden
29138 Posts |
Posted - 11/29/2006 : 17:05:40
|
And VARCHAR2, is that ORACLE datatype?
Peter Larsson
Helsingborg, Sweden |
|
|
|
sojoo
Starting Member
3 Posts |
Posted - 11/29/2006 : 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
Sweden
29138 Posts |
Posted - 11/29/2006 : 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)
USA
6997 Posts |
Posted - 11/29/2006 : 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 - 11/29/2006 : 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
United Kingdom
22191 Posts |
Posted - 11/30/2006 : 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)
USA
6997 Posts |
Posted - 11/30/2006 : 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
United Kingdom
22191 Posts |
Posted - 11/30/2006 : 10:19:53
|
| I think it was the "subtle" bit that you let yourself down with! |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 12/01/2006 : 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 |
|
|
| |
Topic |
|
Stripping a date from a varchar2
