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 ?

[sunny_talwar]

May be some of the [Original Eff Date] are not in YYYYMMDD format, is that true? If that's true, you need to use Alt function for QlikView to understand different type of date formats. If that above is true, can you tell us what all formats do you have for [Original Eff Date] field?

[#3]

The original effective date field is a number field that includes numbers in YYYYMMDD format without exception.

[#4]

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

[sunny_talwar]

If that is true, than I don't see any reason why there should be any nulls. Can you may be do a side by side comparison of each of row to check which values turn to be nulls?

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

          [Original Eff Date]

and then create a table box object to see all those places where Orgeffdate is null.

[#6]

I had already done this, but still could not explain null values.

[sunny_talwar]

How about trying it out like this:

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

[#8]

I copied your script, ran it, got syntax error.

[#9]

I reran your script with the comma at end, script ran fine, but still have null values.

[sunny_talwar]

Would you be able to share few of the dates from the sample above as text and I will try it out at my end.

Best,

Sunny