With regard to DateTimes...
We have a core application, used for legal case management, partly black box. The application always converts the local datetimes, entered into the front end, to UTC Datetime for storage, then converts to local datetime when read. Sensible, it's designed to be used in multiple timezones. We only operate in one timezone, however daylight saving time also causes the conversion of course.
Got that nasty prickly feeling that I've been doing something badly with regard to datetimes for a long time. In my "infancy", and the white heat of becoming Accidental, Lone DBA at the point this system was commissioned, while also having to produce many, many reports from it, I did something I probably should have revisited!
When reporting, we were advised to use the supplied function UTCToLocalTime on dates:
CREATE FUNCTION [dbo].[UTCToLocalTime](@date [DATETIME])
RETURNS [DATETIME] WITH EXECUTE AS CALLER
EXTERNAL NAME [ApplicationNameCLR].[UserDefinedFunctions].[UTCToLocalTime]
Obviously I've never used the function on columns in a JOIN, or WHERE clauses, only in SELECT. The function annoyingly returns '1900-01-01 00:00:00.000' when it encounters a NULL date, so very early on I created (please be gentle):
CREATE FUNCTION [dbo].[udUTCToLocalTime](@date DATETIME)
RETURN NULLIF(dbo.UTCToLocalTime(@date),'1900-01-01 00:00:00.000')
It's been working well, producing accurate results but I'm sure there is a nasty overhead adding milliseconds to reports. Now I'm wondering whether my time (should I ever get any) would be well spent bypassing the assembly and looking for a way to create my second function using native SQL techniques.
(This is the system which caused me to get hauled over hot coals by JC because it uses a CodeLookup table and I wanted to do a wierd, one-off query using an rCTE to produce a hierarchy of the codes 😉 )
https://www.sqlservercentral.com/forums/topic/unusual-recursive-cte-with-non-unique-codes (JC tongue lashing since removed, I see)
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson