How to Compare SQL SmallDateTime Fields for Equality

2017/12/01
By
Modified: 2017/11/05

It is meaningless to compare for equality (==) two fields of type SmallDateTime, because they contain time component and they are hardly ever the same.     Here are some interesting ways around this problem to check, if two SmallDateTime fields have the same date component.

-01- Use BETWEEN
SELECT * FROM tTimeSheet
WHERE Date BETWEEN ’08/4/11′ and ’08/5/11′

-02- Use Date Portion of the date
SELECT * FROM tTimeSheet
WHERE CONVERT(CHAR(10),Date,120) = ‘2011-08-04′

-03- Use DATEPART
SELECT * FROM tTimeSheet
WHERE
DATEPART(YEAR, Date) = ‘2011’ AND
DATEPART(MONTH,Date) = ’08’ AND
DATEPART(DAY, Date) = ’04’

-04- Use Rounding
SELECT * FROM tTimeSheet
WHERE
CAST(FLOOR(CAST(Date AS FLOAT))AS DATETIME) = ‘2011-08-04′

-05- Use LIKE
SELECT * FROM tTimeSheet
WHERE Date LIKE ‘Aug  4 2011%’

*Note two spaces between month and date above: Aug__4.

Tags: , ,


One Response to How to Compare SQL SmallDateTime Fields for Equality

  1. Starly
    2011/11/13 at 15:23

    If inofmartoin were soccer, this would be a goooooal!

Add Your Comment Ваш Комментарий

Your email address will not be published. Required fields are marked *

* Ваше Имя *
* Ваш Email (не будет показан на сайте)*

*