I need a serious kick in the brain here. I've worked with Oracle SQL for years and fairly new to MSSQL and I'm having some serious conceptual issues. Today's is:
I am using a substring to pull a value I am then converting it to a DATE/TIME. As long as data exists, it works fine. When the field is NULL, it reuturns an error that the conversion can't work as data is our of range. This makes sense. So I need to do a conditional select. Something akin to the logic of If the length of the substring is 2 then perform this conversion otherwise just use nothing. I've tried a plethora of IF THEN and CASE WHEN logic but cant seem to tackle this one. How is it done? the select line looks like this:
,convert(varchar,DATEADD(day, CAST(substring(medicalrecordnumber,12,3) AS int) - 1, CONVERT(datetime, substring(medicalrecordnumber,10,2) + '0101', 12)),101) TermDate
Realizing that a very low number of people will have a value in that field. The number it is pulling is a value that looks something like this IG2012123122013001. Now broken down it means
IG 2012 123 12 2013 001
Identifier Start Year Julian Start Day Termination Reason Term Year Julian End Day
Active patients will not have any of the last 9 digits and will have number like IG2012123
Only when they terminate servcie do they get the remaining 9 digits.
I want to create an SQL select statement that can say if the length of the record is 18 then go ahead and pull this info otherwise ignore it.