Search code examples
sqlsql-serversqldatatypes

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.

Solution

  • 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.