How do I re-write this query to programmatically get the date for the start of the day rather than h-CodePudding

Select * from ConsolidatedCashoutTransactionTypes nolock 
where status = 'pendingsettlement' 
and datelogged > '2022-07-06 00:00:00.000' 
order by institution,datelogged

CodePudding user response：

On SQL Server this should work:

Select * from ConsolidatedCashoutTransactionTypes nolock 
where status = 'pendingsettlement' 
and datelogged > CONVERT(DATE, GETDATE()) 
order by institution,datelogged

I'm not sure this works in MySQL too, as you tagged both.

CodePudding user response：

    Select      * 
from        ConsolidatedCashoutTransactionTypes as nolock 
where       status = 'pendingsettlement' 
and         datelogged > cast(current_timestamp as date)
order by    institution
            ,datelogged

This will work on SQL Server. However, I would advise against using nolock as an alias within SQL Server as this is a hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. You will probably notice that this would appear in the text colour as a keyword. More details on nolock can be found at https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

The above code should also work in MySQL (as you did tag both databases) as current_timestamp can be used in that database as well. nolock is not a hint within MySQL.