how to parse a value in the sp? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

New to SQL Server Programming

how to parse a value in the sp?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-11-09 : 11:53:30

I have this sp. I just found out that M.MDS_FILE column, contains a file name as this:
adlu201008261156_3.zip. The submission date is embedded in the file name.

How can I pick out this date? 20100826 this portion and use in the sp? that is the date needed to use here:
instead of GETDATE.

USE [PRO2]
GO
/****** Object: StoredProcedure [dbo].[GET_SCHEDULE_SUBMITTED_DETAIL] Script Date: 11/09/2011 11:04:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[GET_SCHEDULE_SUBMITTED_DETAIL]
@FACILITYKEY varchar(1000),
@UNITSTR VARCHAR(100),
@FromDate datetime,
@ToDate datetime
AS
BEGIN

SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, M.REFERENCE_DATE ,
RTRIM(P.LAST_NAME) + CASE WHEN RTRIM(P.FIRST_NAME) <> '' THEN ', '
ELSE ''
END + RTRIM(P.FIRST_NAME) PATIENT_NAME
,CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE, 'SUBMITTED' AS ASSESSMENTS
FROM [OPTC].MDS_M_SCHEDULE S INNER JOIN OPTC.MD3_M_MAST M
ON S.PAT_NUMBER=M.PAT_NUMBER
LEFT JOIN OGEN.GEN_M_PATIENT_MAST P ON S.PAT_NUMBER = P.PAT_NUMBER
WHERE S.PAT_NUMBER=M.PAT_NUMBER AND M.REFERENCE_DATE < GETDATE()
AND S.A3A_DATE_USER BETWEEN @FromDate AND @ToDate
AND M.SIGN_DATE IS NOT NULL AND M.MDS_FILE IS NOT NULL AND S.FACILITY_KEY IN (SELECT Value FROM dbo.ListToTable(@FACILITYKEY,','))
AND ( @UNITSTR IS NULL
OR @UNITSTR = ''
OR CHARINDEX(P.UNIT_CODE, @UNITSTR)% 2 = 1 )

END

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-09 : 12:02:33

Something like this

declare @filename varchar(32)
set	@filename = 'adlu201008261156_3.zip'

select	Convert(smalldatetime, substring(@filename, charindex('_', @filename) -12, charindex('_', @filename) -9))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources