Convert Date Format

[#1]

I used the following script to convert the date format, but the result included some null values.  How do I correct null values ?

Date(Date#([Original Eff Date],'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate] 

My ultimate objective is to subtract today's date - the original effective date, then divide by 365 to calculate the years insured as follows:

Floor(Today())-Floor(Date(Date#([Original Eff Date],'YYYYMMDD'),'MM/DD/YYYY')))/365 as Yrsinsured

The syntax in the latter script is incorrect, how do I fix it ?

[#2]

I've identified the issue, but still not sure how to resolve.  The script loads two excel files:T1_Active and T2_Term with identical field names.  The script calculates date for T1 but there is no script for T2 date, it is null simply because it was never calculated.  Script editor won't allow me to enter same date calcualtion for T2 date.  My ultimate goal is to calculate the following for T1 and T2.

(Floor(Today())-Floor(Date(Date#([Original Eff Date],'YYYYMMDD'),'MM/DD/YYYY')))/365 as Yrsinsured

I've attached qvw file, T1,T2.

[sunny_talwar]

May be add the same two the second table, right?

T1_Active:

LOAD [Entity Status],

    [Policy Type],

    [Profession Flag],

    [Customer No],

    [Account No],

    [Original Eff Date],

    [Policy Eff Date],

    [Termin Date],

    [Student Flag],

    [Source Code],

    [State Code],

    [Base Premium],

    Date(Date#(Text([Original Eff Date]),'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate]

FROM

Active.xlsx

(ooxml, embedded labels, table is Active);

NoConcatenate

T2_Term:

LOAD [Entity Status],

    [Policy Type],

    [Profession Flag],

    [Customer No],

    [Account No],

    [Original Eff Date],

    [Policy Eff Date],

    [Termin Date],

    [Student Flag],

    [Source Code],

    [State Code],

    [Base Premium],

    Date(Date#(Text([Original Eff Date]),'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate]

FROM

Term.xlsx

(ooxml, embedded labels, table is Term);

[#4]

You fixed the null value issue but I still need to calculate years insured, as mentioned in my earlier replies.  You’ve been so helpful that I feel bad asking for additional help.  Should I ask for your help with the years insured calculation ?

[sunny_talwar · Accepted solution]

This?

T1_Active:

LOAD [Entity Status],

    [Policy Type],

    [Profession Flag],

    [Customer No],

    [Account No],

    [Original Eff Date],

    [Policy Eff Date],

    [Termin Date],

    [Student Flag],

    [Source Code],

    [State Code],

    [Base Premium],

    Date(Date#(Text([Original Eff Date]),'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate],

    (Floor(Today())-Floor(Date#([Original Eff Date],'YYYYMMDD')))/365 as Yrsinsured

FROM

Active.xlsx

(ooxml, embedded labels, table is Active);

NoConcatenate

T2_Term:

LOAD [Entity Status],

    [Policy Type],

    [Profession Flag],

    [Customer No],

    [Account No],

    [Original Eff Date],

    [Policy Eff Date],

    [Termin Date],

    [Student Flag],

    [Source Code],

    [State Code],

    [Base Premium],

    Date(Date#(Text([Original Eff Date]),'YYYYMMDD'),'MM/DD/YYYY') as [Orgeffdate],

    (Floor(Today())-Floor(Date#([Original Eff Date],'YYYYMMDD')))/365 as Yrsinsured

FROM

Term.xlsx

(ooxml, embedded labels, table is Term);

[#6]

You did it.  I had already run similar script, which may not have worked because my software version is a free version which expired.  I just installed the licensed version about 10 minutes ago and the script works great.

THANKS !!  Your techinical support is outstanding.

[#7]

My script was simalar but incorrect, so I get no credit for resolving this, you get all the credit.  THANKS AGAIN !!