Date Interval

[Adrian1979]

I have a date 20211119.

It is read in as a integer, so I have converted it into a varchar using in the ETL to create a qvd file;

convert (varchar(10),creditapproveddate) as CAD_fmt

With this .qvd file I have translated it into a date using;

Date(Date#(CAD_fmt,'yyyymmdd'),'dd/mm/yyyy')

This appears to work, as I can use Year(Date(Date#(CAD_fmt,'yyyymmdd'),'dd/mm/yyyy')) to extract the year correctly.

Here's where things get a bit weird.

The month statement always returns 'Jan'.

And when I try to find the difference between Date(Date#(CAD_fmt,'yyyymmdd'),'dd/mm/yyyy') and Date(Now()) using Interval, I get results which I simply don't understand.

Is there a way to check that I have actually converted my field into a date?

[Or · Accepted solution]

Sounds like you'd have an easier time if you just used MakeDate(left(date,4),mid(date,5,2),right(date,2)) or the numeric equivalent using mod() and div().

In general, subtracting a date from another date will return the interval (in days), so if both of your dates are correct, this should behave consistently and as expected.

[Adrian1979]

Thank you, that works a treat.  I just can't understand the logic of why though!