>I am working with the SQL Server database. The field/column is DateTime. And I want to get records by the following criteria:
>
>
>select * from MyTable where DateTimeCol <= '20170810'
>
>
>Since the DateTimeCol has both the date and time, the above will select 0 records.
>
>How should I change the WHERE to make sure that only the Date of DateTime is considered and therefore the records where the Date is '20170810' are returned?
You would usually use greater or equal from the start of the day and less or equal from the end of the day. I have some easy to convert to VFP methods like this to return the start and end dates of a day:
' Return a date starting on the day
' expT1 Date
Public Function GetDateDayStart(ByVal tdDate As Date) As Date
Return New Date(tdDate.Year, tdDate.Month, tdDate.Day, 0, 0, 0)
End Function
' Return a date ending on the day
' expT1 Date
Public Function GetDateDayEnd(ByVal tdDate As Date) As Date
Return New Date(tdDate.Year, tdDate.Month, tdDate.Day, 23, 59, 59)
End Function
However, in GetDatDayEnd(), you would need to consider high precision at the millisecond level in case you would have a record which was created at 23:59:59 123.
Another way is to use greater than or equal to the start of the day and less than of that start date + 1 day.