Text to date conversions and filters

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

SQL Server 2008 Forums

Transact-SQL (2008)

Text to date conversions and filters

Author

Topic

KJinTN
Starting Member

2 Posts

Posted - 2012-10-17 : 10:24:03

I'm writing a query that's going to be used to populate a report. The report needs to identify patients who were hospitalized during the date range the users select (for now, just trying to get the query right, then I'll worry about adding prompts).

The problem is that the hospitalization dates are stored A) vertically - the admit date is in one row and the discharge date in another; and B) they're stored as text.

I've been able to convert the text to a date-formatted display, and flatten the records, by doing this:

MAX(CASE WHEN FindingAbbr = 'AR_HospAdmDate' then SUBSTRING(CONVERT(VARCHAR, dbo.HObservation.Value, 12), 5, 2) + '/' + SUBSTRING(CONVERT(VARCHAR, dbo.HObservation.Value, 12), 7, 2) + '/' + LEFT(CONVERT(VARCHAR, dbo.HObservation.Value, 102), 4) else null end) as HospitalAdmitDate

I imagine I'll need to use a HAVING clause, since I'm using MAX(CASE) to flatten the records, but how do I write the clause? I've tried:

MAX(CASE WHEN FindingAbbr = 'AR_HospAdmDate' then SUBSTRING(CONVERT(VARCHAR, dbo.HObservation.Value, 12), 5, 2) + '/' + SUBSTRING(CONVERT(VARCHAR, dbo.HObservation.Value, 12), 7, 2) + '/' + LEFT(CONVERT(VARCHAR, dbo.HObservation.Value, 102), 4) else null end) < '07/01/2012'

but it's not filtering the records at all. I'm well and truly stumped.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-17 : 11:02:55

Make your current query a subquery like shown below

DECLARE @dateRangeStart DATETIME, @dateRangeEnd DATETIME;
SET @dateRangeStart = '20120101';
SET @dateRangeEnd = '20120131'; 
    
SELECT * FROM
( 
	-- Your current query in green
	SELECT PatientId,
		   HospitalAdmitDate,
		   HosptialDischargeDate
	FROM   YourTable     
)s
WHERE
	HospitalAdmitDate BETWEEN @dateRangeStart AND @dateRangeStart
	OR HosptialDischargeDate BETWEEN @dateRangeStart AND @dateRangeStart

KJinTN
Starting Member

2 Posts

Posted - 2012-10-17 : 12:22:56

quote:
Originally posted by sunitabeck

Make your current query a subquery like shown below

Why didn't I think of that? ::sigh:: Thanks a bunch!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources