cast datetime to date in where statements

[Derit Agustin]

how to cast datetime field into date?

an example :

select transaction_datetime from sale_transaction where date(transaction_datetime)='2018/02/28'

in my code

 .Add(Restrictions.Eq('transaction_datetime', formatdatetime('yyyy/mm/dd', now)))

that not cast by date, but datatype datetime in where statement

[Stefan Glienke]

What exactly is the resulting SQL generated?

[Derit Agustin]

select transaction_datetime from sale_transaction where date(transaction_datetime) = '2018/02/28'

i have a data transaction_datetime

• 2018/02/28 18:30:30

• 2018/02/28 18:30:31

• 2018/02/28 18:30:32

i need where only date 2018/02/28

[Stefan Glienke]

Well obviously then the SQL you posted is not the generated because that would be what you want, no?

[Derit Agustin]

yes, right

[DiGi]

Your SQL code looks bad. SQL Server probably have to read every row, do type conversion and after that compare result, giving up on indexes. Result will be bad (slow) execution plan.

I tested your code on fully updated MS SQL Server 2016 Enterprise

• source table have 159 million rows
• there is filtered index on column Created, server doesn't have any better source data
• tested on dedicated production system, 80 CPU cores, 6TB RAM. So difference on less powerful systems will be bigger
• I'm using COUNT to get number (not list of IDs) to eliminate server-client transfer

-- Your code
SELECT COUNT(1) AS Totals FROM dbo.Documents AS D WITH(NOLOCK) WHERE CAST(D.Created AS DATE) = '20180301' /* This is only for filtered index: */ AND D.Created > '20160101'

-- Better code
SELECT COUNT(1) AS Totals FROM dbo.Documents AS D WITH(NOLOCK) WHERE D.Created >= '20180301' AND D.Created < '20180302'

First Select result:

Table 'Documents'. Scan count 33, logical reads 246510, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4236 ms,  elapsed time = 410 ms.

Index Scan(OBJECT:([dbo].[Documents].[IX_Documents_Created] AS [D]),  WHERE:(CONVERT(date,[dbo].[Documents].[Created] as [D].[Created],0)='2018-03-01'))

• whole index was read (69mil) to get 99 thousands of rows
• without filtering in index there will be 159mil reads and execution will be even slower
• play was executed in parallel (32 cores). Data was returned in 410ms but it takes 4236ms to compute

Second select:

Table 'Documents'. Scan count 1, logical reads 242, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 6 ms.

Index Seek(OBJECT:([dbo].[Documents].[IX_Documents_Created] AS [D]), SEEK:([D].[Created] >= '2018-03-01 00:00:00.000' AND [D].[Created] < '2018-03-02 00:00:00.000') ORDERED FORWARD)

• scans only exact part of index
• single core plan

And this is only to get one number. 4236ms vs 0 (6 in total).

I think that another SQL engines will not performs much better or different.

[Cesar Romero]

First, I don't see this anyway related to spring, you will have the same problem with any component.

What I use to do when I had to migrate an old system from FB to MSSQL, was modify the time part like this:

if you want that the query return records from '20180301' to < '20180302' inclusive, you have to make sure to, make sure to fix the time part of both, in 2018-03-01 00:00:00.000 and 2018-03-02 23:59:59.999, here you must format using your locale settings, using AM/PM or 24 day hours, I did using mine.

SELECT COUNT(1) AS Totals FROM dbo.Documents AS D WITH(NOLOCK) WHERE D.Created BETWEEN '2018-03-01 00:00:00.000' AND '2018-03-02 23:59:59.999'

I suggest you to create Delphi functions that take care of that, and use it when setting the query params.

[DiGi]

My response is related to Spring: Generated SQL code should not be bad. Provided example with type casting is bad. It could seriously impact performance of application.

About my MSSQL example:

• using YYYYMMDD format is intentional, because format parts are fixed. In your example value depends on client locate, so for some locates will work, for another will crash ("The conversion of a varchar data type to a datetime data type resulted in an out-of-range value"). I don't need take care about locate, AM/PM...
• you probably want write BETWEEN '2018-03-01 00:00:00.000' AND '2018-03-01 23:59:59.999', just one whole day
• but your example is not working for DATETIME2 type, because you can have 23:59:59.9990001. Example in sqlfiddle
• Note: BETWEEN is just syntax sugar for >= and < if you check plans - SQL Server smart enough to figure that in DATETIME case you want D.Created < 20180302

Agustin requested additional type conditional for filtering by date. He suggested type conversion as generated SQL code, but that SQL code will impact performance.

[Cesar Romero]

If you are using params, the locale do not matter, does? my point is, if the index is for datetime, cast to date will have bad performance as you pointed, if you fix the param, there is no cast, and the rest is up to the database.

[DiGi]

Yeah, I wrote about performance issue and forgot to write clear suggestion.

There could be option to compare only date part of DATETIME field. I'm not sure if it should be Restrictions.Eq(...) (and detect TDate) or something new like Restrictions.EqDate(...).

• Value is type TDate
• If target column is DATE type, generated code could be simple T0.FieldName = :Value
• If target column is DATETIME or another more precise type then Spring should generate code like (T0.FieldName >= :Value AND T0.FieldName < :Value+1). This code could work for sql DATE type too, but it is quite redundant
• Do not generate suggested DATE(T0.FieldName) = :Value (performance issue)
• Do not generate BETWEEN (issue with correct locate, issue with DATETIME2)

[Stefan Glienke]

Recent versions of SQL Server know to use the index when you do a cast of datetime to date.

[Stefan Glienke]

The performant solution with >= and < can be modelled using the existing criteria API. If you with you can add your own helper method for Restrictions to give you a more convenient EqDate method or similar)