lnorthern lnorthern - 4 months ago 16
SQL Question

How do I workaround datetime2's explicit conversion policy

I created a report a while ago which is used to return data from an Audit table which gathers login and logout activity on a trading platform.

I began to have issues with a certain string following the implementation of

datetime2
datatypes one the date columns. Specifically the string was used to gather various bits of information about how active a user was during a given month, being used to denote their number of logins and logouts, the number of days they were active for before logging out following a log in etc etc.

CAST(MAX(FLOOR(CAST(bus.SessionTimestamp AS FLOAT))) AS DATETIME) as SessionDate


The issues are due to the conversion of
datetime2
to
float
no longer being an option, as
datetime
used to be.

How can I get the above string to work in a similar way but actually function properly again?

Thanks everyone in advance.

Answer Source

Once you realise what this code is trying to do, it's a trivial conversion:

MAX(CAST(bus.SessionTimestamp AS DATE)) as SessionDate

The FLOOR after converting to float is just removing the time component. Up to you whether you reinstate the CAST back to datetime2 after computing the MAX.