Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
Thank you, that works a treat. I just can't understand the logic of why though!