Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
190 views
in Technique[技术] by (71.8m points)

c# - Result output varied in date time query

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...