March 3, 2014 at 3:22 pm
I am trying to construct a query to parse out the Date in a string, then convert that result to a DATETIME, Then add 14 to that result. Once this is done I need to convert the resulted datetime back to a string and add it back into the end of a string.
Example:
SELECT Descr
FROM DayOfMonth
The Result would look something like this "The Last Wednesday of February was 02/21/14'
so far I have been able to parse this out by using this query:
SELECT RIGHT(Descr, PATINDEX('%[0-9]%',Descr ) -34) AS DATE
FROM DayOfMonth
This Gives me the 02/21/14 I'm looking for. As a separate Query I know how to convert this to DATETIME SELECT CONVERT (DATETIME, '02/21/14',1)
Then I would just add +14
SELECT
(
SELECT CONVERT (DATETIME, '02/21/14',1)
) + 14 AS DATE
The last part would be to convert this back to string and insert it into another longer string
EXAMPLE: 'The Date two weeks after last Wednesday of month is 03/07/14'
March 3, 2014 at 4:36 pm
Hi
You just need to CONVERT back once you've added 14 days, so 'some string' + CONVERT(VARCHAR(8), yourupdateddate,1)
As a note, I would use the DATEADD function and make you pattern search more restrictive to avoid hitting numbers that aren't part of the date.
So all up something like
-- Couple of sample strings
WITH SampleStrings AS (
SELECT s1
FROM (VALUES
('This is the 1st sample containing 12/03/14'),
('This is the 2nd one containing 02/21/14 with other stuff')
) S(s1)
)
SELECT s1,
'Some other string added to the beginning ' +
-- convert back to varchar with same format
CONVERT( VARCHAR(20),
-- add 14 days
DATEADD(day,
14,
-- convert to date
CONVERT(DATETIME,
-- Parse out date component (must be mm/dd/yy)
SUBSTRING(
s1,
-- Note this pattern could still get invalid dates, but not numbers
PATINDEX('%[01][0-9]/[0-3][0-9]/[0-9][0-9]%',s1), -- Find beginning of date
8 -- Length of date (always 8 if format mm/dd/yy)
)
,1)
)
,1)
FROM SampleStrings
March 4, 2014 at 11:47 am
Thanks for this, but i was unable to make it work with my query and DB. I finally asked my boss and he gave me this which worked. I am posting it here only so other people reading might like to see it.
UPDATE dbo.VS_DESCRIPTION
SET Descr = 'Report Period Ending '
+ CONVERT(VARCHAR(10),DATEADD(DD,14,CAST(RIGHT(Descr, PATINDEX('%[0-9]%',Descr ) -34) AS DATE)),1)
March 4, 2014 at 2:26 pm
Really that isn't a great deal different from what I posted. My biggest concern is with the portion to parse the date out of the string. RIGHT(Descr, PATINDEX('%[0-9]%',Descr ) -34)
The string provided in the original post
The Last Wednesday of February was 02/21/14
0000000001111111111222222222233333333334444
1234567890123456789012345678901234567890123
will fail with expression you provided results with 36 - 34 = 2 and RIGHT('The Last Wednesday of February was 02/21/14',2) returns 14.
SELECT RIGHT('The Last Wednesday of February was 02/21/14', PATINDEX('%[0-9]%','The Last Wednesday of February was 02/21/14' ) - 34)
If you always have the date at the end of the string and it is always in the format mm/dd/yy then you would be better to use RIGHT(Descr,8)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply