c# - Result output varied in date time query

Question

i have a query which is generated from dotnet program where i am passing date in SearchString parameter as follows

 Public Function Getdata(ByVal d As Date) As DataTable 

WHERE (IC.ICD_UPDATE_DT IS NULL OR IC.ICD_UPDATE_DT > :SearchString and IC.Icd_Create_Dt < SearchString))
cmdCR.Parameters.AddWithValue(":SearchString", d.ToString("dd-MMM-yyyy"))

it results no rows 39

while i am checking it at query side manually as below it results only 30 rows as follows

 WHERE (IC.ICD_UPDATE_DT IS NULL OR trunc(IC.ICD_UPDATE_DT) > '30-nov-2020' )
         and trunc(IC.Icd_Create_Dt) < '30-nov-2020' )

so i am not able to find why is there different output for same condition. Any idea would be appreciated.

question from:https://stackoverflow.com/questions/65950598/result-output-varied-in-date-time-query

Answer 1

There are 2 trunc() in the where below and missing in the query above.

Using trunc() against a date cuts off the time and makes dates like '01.12.2021 01:23:34' to look like '01.12.2021 00:00:00'.

Thus, lines having icd_create_dt = '30-nov-2020 13:24' will be in the output of the first query and be shown in your test query