CREATE VIEW INFORMZ_VIEW_DATE_FORMAT
AS
SELECT N.ID as ID, 
N.JOIN_DATE,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
CONVERT(VARCHAR(10), N.JOIN_DATE, 101) END as JOIN_DATE_US,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
CONVERT(VARCHAR(10), N.JOIN_DATE, 103) END as JOIN_DATE_EU,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
CONVERT(VARCHAR(10), N.JOIN_DATE, 114) END as JOIN_DATE_T24,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
DATEPART(MM, N.JOIN_DATE) END as JOIN_DATE_MM,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
DATEPART(DD, N.JOIN_DATE) END as JOIN_DATE_DD,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
DATEPART(YYYY, N.JOIN_DATE) END as JOIN_DATE_YYYY,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
RIGHT(DATEPART(YYYY, N.JOIN_DATE),2) END as JOIN_DATE_YY,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
DATENAME(MM, N.JOIN_DATE) END as JOIN_DATE_MN,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
LEFT(DATENAME(MM, N.JOIN_DATE),3) END as JOIN_DATE_MN_ABR,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
DATENAME(DW, N.JOIN_DATE) END as JOIN_DATE_DOW,
CASE WHEN N.JOIN_DATE IS NOT NULL THEN
LEFT(DATENAME(DW, N.JOIN_DATE),3) END as JOIN_DATE_DOW_ABR,
N.PAID_THRU, 
CASE WHEN N.PAID_THRU IS NOT NULL THEN
CONVERT(VARCHAR(10), N.PAID_THRU, 101) END as PAID_THRU_US,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
CONVERT(VARCHAR(10), N.PAID_THRU, 103) END as PAID_THRU_EU,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
CONVERT(VARCHAR(10), N.PAID_THRU, 114) END as PAID_THRU_T24,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
DATEPART(MM, N.PAID_THRU) END as PAID_THRU_MM,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
DATEPART(DD, N.PAID_THRU) END as PAID_THRU_DD,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
DATEPART(YYYY, N.PAID_THRU) END as PAID_THRU_YYYY,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
RIGHT(DATEPART(YY, N.PAID_THRU),2) END as PAID_THRU_YY,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
DATENAME(MM, N.PAID_THRU) END as PAID_THRU_MN,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
LEFT(DATENAME(MM, N.PAID_THRU),3) END as PAID_THRU_MN_ABR,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
DATENAME(DW, N.PAID_THRU) END as PAID_THRU_DOW,
CASE WHEN N.PAID_THRU IS NOT NULL THEN
LEFT(DATENAME(DW, N.PAID_THRU),3) END as PAID_THRU_DOW_ABR,
N.MEMBER_STATUS_DATE, 
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
CONVERT(VARCHAR(10), N.MEMBER_STATUS_DATE, 101) END as MEMBER_STATUS_DATE_US,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
CONVERT(VARCHAR(10), N.MEMBER_STATUS_DATE, 103) END as MEMBER_STATUS_DATE_EU,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
CONVERT(VARCHAR(10), N.MEMBER_STATUS_DATE, 114) END as MEMBER_STATUS_DATE_T24,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
DATEPART(MM, N.MEMBER_STATUS_DATE) END as MEMBER_STATUS_DATE_MM,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
DATEPART(DD, N.MEMBER_STATUS_DATE) END as MEMBER_STATUS_DATE_DD,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
DATEPART(YYYY, N.MEMBER_STATUS_DATE) END as MEMBER_STATUS_DATE_YYYY,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
RIGHT(DATEPART(YY, N.MEMBER_STATUS_DATE),2) END as MEMBER_STATUS_DATE_YY,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
DATENAME(MM, N.MEMBER_STATUS_DATE) END as MEMBER_STATUS_DATE_MN,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
LEFT(DATENAME(MM, N.MEMBER_STATUS_DATE),3) END as MEMBER_STATUS_DATE_MN_ABR,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
DATENAME(DW, N.MEMBER_STATUS_DATE) END as MEMBER_STATUS_DATE_DOW,
CASE WHEN N.MEMBER_STATUS_DATE IS NOT NULL THEN
LEFT(DATENAME(DW, N.MEMBER_STATUS_DATE),3) END as MEMBER_STATUS_DATE_DOW_ABR,
NF.RENEWED_THRU,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
CONVERT(VARCHAR(10), NF.RENEWED_THRU, 101) END as RENEWED_THRU_US,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
CONVERT(VARCHAR(10), NF.RENEWED_THRU, 103) END as RENEWED_THRU_EU,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
CONVERT(VARCHAR(10), NF.RENEWED_THRU, 114) END as RENEWED_THRU_T24,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
DATEPART(MM, NF.RENEWED_THRU) END as RENEWED_THRU_MM,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
DATEPART(DD, NF.RENEWED_THRU) END as RENEWED_THRU_DD,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
DATEPART(YYYY, NF.RENEWED_THRU) END as RENEWED_THRU_YYYY,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
RIGHT(DATEPART(YYYY, NF.RENEWED_THRU),2) END as RENEWED_THRU_YY,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
DATENAME(MM, NF.RENEWED_THRU) END as RENEWED_THRU_MN,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
LEFT(DATENAME(MM, NF.RENEWED_THRU),3) END as RENEWED_THRU_MN_ABR,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
DATENAME(DW, NF.RENEWED_THRU) END as RENEWED_THRU_DOW,
CASE WHEN NF.RENEWED_THRU IS NOT NULL THEN
LEFT(DATENAME(DW, NF.RENEWED_THRU),3) END as RENEWED_THRU_DOW_ABR 
FROM name n WITH (NOLOCK) 
INNER JOIN name_fin nf with (NOLOCK) on n.id = nf.id
LEFT OUTER JOIN name_address na WITH (NOLOCK) on n.mail_address_num = na.address_num
WHERE ((n.email <> '' and n.email is not null) or EXISTS
(select id from name_address na WITH (NOLOCK) where n.id = na.id and na.email <> '' and na.email is not null))
 AND n.status in (select status from informz.informz_func_personalization())