0

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

Paul T.
  • 4,703
  • 11
  • 25
  • 29
atdower
  • 1
  • What dbms is it and what is the type of column `submissiondate`? – markalex Mar 23 '23 at 22:42
  • if it is a date time, try `BETWEEN '2020-01-01 0:00:00' AND '2022-12-31 23:59:59'` . Applying expressions on the table columns in a where or join condition will make it impossible for the query optimiser to generate an optimal query plan. – marcothesane Mar 23 '23 at 22:57
  • It is SQL Server - The submissiondate column is a date time field. – atdower Mar 23 '23 at 23:33
  • @marcothesane - Be careful with data/time edge conditions `BETWEEN '2020-01-01 0:00:00' AND '2022-12-31 23:59:59'` will omit date/times like `'2022-12-31 23:59:59.500'` and others within that final second of the year. Better would be to use of exclusive end dates as in `where rf.submissiondate >= '20200101' and rf.submissiondate < '20230101'`. Also (contrary to common belief), SQL Server can efficiently perform an index seek through a `CAST ... AS DATE)` conversion. See [this post](https://dba.stackexchange.com/questions/34047/cast-to-date-is-sargable-but-is-it-a-good-idea) . – T N Mar 25 '23 at 04:23

2 Answers2

2

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))
gotqn
  • 42,737
  • 46
  • 157
  • 243
0

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

Rogx
  • 11
  • 2