• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1493
  • Last Modified:

How to split date from string data

I have these kind of data from a varchar column:
Recommended By: Jegonia, Erika @ 9/16/2006 10:07:48 AM
Approved By: Jegonia, Jimi @ 7/7/2006 8:09:00 AM

How can split them so I can get both values:
Sample 1:
-LDate = 9/16/2006 10:07:48 AM
-SDate = 9/16/2006

Sample 2:
-LDate = 7/7/2006 8:09:00 AM
-SDate = 7/7/2006

Thanks
0
JaimeJegonia
Asked:
JaimeJegonia
  • 4
  • 2
1 Solution
 
Pratima PharandeCommented:
try like this

Declare @str varchar(100)
set @str = 'Jegonia, Erika @ 9/16/2006 10:07:48 AM'
SELECT Right(@str,Len(@str) - CHARINDEX ( '@', @str)) as LDate ,
Substring(@str,CHARINDEX ( '@', @str)+2,10) as Sdate
0
 
Pratima PharandeCommented:
Declare @str varchar(100)
set @str = 'Jegonia, Erika @ 1/1/2006 10:07:48 AM'
SELECT Right(@str,Len(@str) - CHARINDEX ( '@', @str)) as LDate ,
Substring(@str,CHARINDEX ( '@', @str)+2,CHARINDEX ( ':', @str)-CHARINDEX ( '@', @str)-4) as Sdate
0
 
JaimeJegoniaAuthor Commented:
How can I dynamically refer to the column name (say ApprovedDate) and I dont want to declare anything. I want to be able to create a new concatenated column.

Thanks
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
JaimeJegoniaAuthor Commented:
I tried to substitute @str with AppNameAndDate in the view grid as follows and I got the attached error:

SELECT SUBSTRING(AppNameAndDate, CHARINDEX('@', AppNameAndDate) + 2, CHARINDEX(':', AppNameAndDate) - CHARINDEX('@', AppNameAndDate)
               - 4) AS SDate, RIGHT(AppNameAndDate, LEN(AppNameAndDate) - CHARINDEX('@', AppNameAndDate)) AS LDate
FROM  dbo.tblPR


SQLError.JPG
0
 
JaimeJegoniaAuthor Commented:
With this one, I'm actually getting the long date right.
RIGHT (AppNameAndDate, LEN(AppNameAndDate) - CHARINDEX('@', AppNameAndDate))

Now, Is there an MS SQL function to convert or format to make that long date to short date?

Thanks  
0
 
JaimeJegoniaAuthor Commented:
Dear Moderator,

please remove this question - I have changed my approach to go on.

Thanks

Jimi Jegonia
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now