CODEWITHSUNDEEP
sqlcastingnestedintersystems-cache
mkelsh
mkelsh

Reputation: 1

Nesting a CAST() Function

I am attempting create a nested CAST() function and am struggling with the syntax. My simplified code so far:

SELECT 
discharge_date,
admission_date,
Patient_Uniue_ID,
CAST(CAST(Year(admission_date) AS VarChar(4)) + '-' + CAST(Month(admission_date) AS VarChar(2)) + '-' + CAST(Day(admission_date) AS VarChar(2)) AS DATE) AS 'Due_Date'

FROM   SYSTEM.client_history

WHERE DateDiff('d',Due_Date, GetDate()) <= 14

Trying to get the casted due date to be set yearly on the anniversary of admission, but I get an "invalid SQL format" and "Option expected, IDENTIFIER(DATEFORMAT)" error. Is there a better way to approach this?

Upvotes: -1

Views: 734

Answers (1)

imagine that
imagine that

Reputation: 36

I think this might do the trick.

There are two redundant columns in the query below that show my workings to get the anniverary date for both this year and next year. Both add the difference between the current year and the admission year, with an extra year added to get next year.

I'm using the same calculated dates within a CASE statement. If the anniversary date for this year is less than '14 days in the future' then the anniversary is next year date. Otherwise it is this year.

select admission_date
, CAST (DateAdd('yyyy', (Year(GETDATE())-Year(admission_Date)),admission_date) as DATE) as 'admission_date_THIS_YEAR'
, CAST (DateAdd('yyyy', (Year(GETDATE())-Year(admission_Date)+1),admission_date) as DATE) as 'admission_date_NEXT_YEAR'
, CAST (CASE WHEN (DateAdd('yyyy', (Year(GETDATE())-Year(admission_date)),admission_date)
                   < DateAdd('dd',14,GETDATE()))
             THEN DateAdd('yyyy', (Year(GETDATE())-Year(admission_date)+1),admission_date)
             ELSE DateAdd('yyyy', (Year(GETDATE())-Year(admission_date)),admission_date)
  END as DATE) As 'Admission_Anniversary'
FROM SYSTEM.client_history

You might not need the CAST (... as DATE) but it tidied up the output within the SQL editor in the Caché Portal when I tested this, displaying a date rather than a timestamp. You also might need to change the 14 day test to 15 days. I think it works for future admissions as well, if that scenario could occur.

Upvotes: 1

Related Questions