James makes an easy-to-make mistake
28/02/2022
a screenshot of a social media post
I fell fully into this one recently whilst trying to figure out was up with the script I had produced for assigning the Date for the Client’s next Financial Assessment as part of a data migration project into ContrOCC.

I spent time troubleshooting a non-existent issue in a Rank() statement that I was using to extract the most recent completed assessment date that I wanted to add a year to, before realising the issue was  in fact with my interval.

I am sure that using the last letter rather than the first letter of the description for an abbreviation makes heaps of sense to someone in the Microsoft  development team, but for me it took a while to consider that y would stand for day rather than year!

Funnily enough in the end we decided to change it to weeks in instead of 1 year anyway ( to avoid scheduling the next review on a  weekends and to match the ContrOCC functionality).

Turns out you have to beware of as well because this adds weekdays.  (Very handy, though not immediately extendable to include bank holidays or other special days to avoid as well as Saturdays and Sundays, so not quite workingdays )

So in the end I plumped for:

  DATEADD(Week,52,fa.[DateAssessmentCarriedOut])

Now of course as a data migration specialist who has been working with SQL Server for over a decade, I should have known this already, but sometimes I have to admit I do find myself assuming things, that then appear to work ( or at least don't produce an obvious error right away.)

I work across Oracle, MySQl and Sql Server, VBA, Javascript, PHP and Dax so it is inevitable that sometimes there is a bit of cross-contamination of syntax. Nevertheless I hate wasting time if it is avoidable so have resolved in future to always try and use the full name for arguments such as this (Year, Week , Month etc) instead of saving a millisecond or two using an abbreviation.

By using keyintelligence.uk, you agree to our use of cookies to enhance your experience.