Compare DATETIME and DATE ignoring time portion

Question

I have two tables where column  date  is type of DATETIME2 0    I have to compare two records only by theirs Date parts  day month year   discarding Time parts  hours minutes seconds    How can I do that

User · Answer

For Compare two date like MM DD YYYY to MM DD YYYY     Remember First thing column type of Field must be dateTime   Example   columnName    payment date      dataType   DateTime    after that you can easily compare it   Query is     select     from demo date where date  gt    3 1 2015  and date  lt     3 31 2015     It very simple         It tested it

User · Answer

A small drawback in Marc s answer is that both datefields have been typecast  meaning you ll be unable to leverage any indexes   So  if there is a need to write a query that can benefit from an index on a date field  then the following  rather convoluted  approach is necessary    The indexed datefield  call it DF1  must be untouched by any kind of function  So you have to compare DF1 to the full range of datetime values for the day of DF2  That is from the date-part of DF2  to the date-part of the day after DF2  I e   DF1  gt   CAST DF2 AS DATE   AND  DF1  lt  DATEADD dd  1  CAST DF2 AS DATE    NOTE  It is very important that the comparison is     equality allowed  to the date of DF2  and  strictly   lt  the day after DF2  Also the BETWEEN operator doesn t work because it permits equality on both sides    PS  Another means of extracting the date only  in older versions of SQL Server  is to use a trick of how the date is represented internally    Cast the date as a float  Truncate the fractional part Cast the value back to a datetime I e  CAST FLOOR CAST DF2 AS FLOAT   AS DATETIME

User · Answer

You can try this one  CONVERT DATE  GETDATE      CONVERT DATE  2017-11-16 21 57 20 000     I test that for MS SQL 2014 by following code  select case when CONVERT DATE  GETDATE      CONVERT DATE  2017-11-16 21 57 20 000   then  ok              else    end

User · Answer

You may use DateDiff and compare by day  DateDiff dd  date1  date2   gt  0  It means  date2  gt   date1 For example   select DateDiff dd   01 01 2021 10 20 00    02 01 2021 10 20 00     has the result   1

User · Answer

Though I upvoted the answer marked as correct  I wanted to touch on a few things for anyone stumbling upon this   In general  if you re filtering specifically on Date values alone  Microsoft recommends using the language neutral format of ymd or y-m-d       Note that the form  2007-02-12  is considered language-neutral only   for the data types DATE  DATETIME2  and DATETIMEOFFSET    To do a date comparison using the aforementioned approach is simple  Consider the following  contrived example   --112 is ISO format  YYYYMMDD  declare  filterDate char 8    CONVERT char 8   GETDATE    112   select         from      Sales Orders where     CONVERT char 8   OrderDate  112     filterDate   In a perfect world  performing any manipulation to the filtered column should be avoided because this can prevent SQL Server from using indexes efficiently  That said  if the data you re storing is only ever concerned with the date and not time  consider storing as DATETIME with midnight as the time  Because      When SQL Server converts the literal to the filtered column   s type  it   assumes midnight when a time part isn   t indicated  If you want such a   filter to return all rows from the specified date  you need to ensure   that you store all values with midnight as the time    Thus  assuming you are only concerned with date  and store your data as such  The above query can be simplified to   --112 is ISO format  YYYYMMDD  declare  filterDate char 8    CONVERT char 8   GETDATE    112   select         from      Sales Orders where     OrderDate    filterDate

User · Answer

Use the CAST to the new DATE data type in SQL Server 2008 to compare just the date portion   IF CAST DateField1 AS DATE    CAST DateField2 AS DATE

[sql] Compare DATETIME and DATE ignoring time portion

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to datetime-conversion