SQL Server How to convert date time to date [duplicate]

0md85ypi  于 2023-03-28  发布在  其他
关注(0)|答案(2)|浏览(140)

This question already has answers here:

How to return only the Date from a SQL Server DateTime datatype (46 answers)
How can I truncate a datetime in SQL Server? (15 answers)

Closed 3 days ago.

I am a novice SQL user and am trying to figure out how I convert a date time field to date only.

Here is my query:

Select ed.procedurecode, ed.encounterid, rf.submissiondate
from encounterdetail as ed
join receivedfile as rf on ed.submissiondatekey=rf.submissiondatekey
where rf.submissiondate between '2020-01-01' and '2022-12-31' --This field is a date time field which I am trying to convert to a date so I can find all values between these dates**
and `rf.statustypeid = '1'`

The query will not pull the dates between since I am not including the hours, min,sec

wsewodh2

wsewodh21#

Try this:

Select ed.procedurecode, ed.encounterid, rf.submissiondate
from encounterdetail as ed
join receivedfile as rf on ed.submissiondatekey=rf.submissiondatekey
where rf.submissiondate >= '2020-01-01' 
    AND rf.submissiondate < '2023-01-01' --This field is a date time field which I am trying to convert to a date so I can find all values between these dates**
and rf.statustypeid = '1'

Basically, we are looking for all days greater or equal to your start date:

>= '2020-01-01'

and less then your end date plus one day:

< '2023-01-01'

If the last is passed as argument to routine, you can calculate it dynamically like this:

SELECT DATEADD(DAY, 1, CAST('2022-12-31' AS DATE))
shyt4zoc

shyt4zoc2#

Like with a lot of SQL stuff, the best solution is dependent on the volume of data, the necessity to scale, etc. so without knowing a lot about your product/project it will be hard to determine what is the best path to take... That being said, if you are using 2008+ then a simple solution would be to wrap it in a CAST in your WHERE clause. Normally I would tend to stay away from adding/using functions in a WHERE clause, but if this is a one-off or a rarely used query where performance is not a concern then I would try something similar to this:

Select ed.procedurecode, ed.encounterid, rf.submissiondate
from encounterdetail as ed
join receivedfile as rf on ed.submissiondatekey=rf.submissiondatekey
where CAST(rf.submissiondate AS Date) between '2020-01-01' and '2022-12-31' 
and rf.statustypeid = '1'

Remember that 'Between' is inclusive so it pulls both the conditions into the result set. If you do not want those dates, meaning excluding the upper and lower bounded dates, then I would go with the > < suggestion that @gotqn put in their WHERE Clause

相关问题