How to query DATETIME field using only date in Microsoft SQL Server

Question

I have a table TEST with a DATETIME field  like this  ID NAME DATE 1 TESTING 2014-03-19 20 05 20 000  What I need a query returning this row and every row with date 03 19 2014  no matter what the time is  I tried using select   from test where date    03 19 2014    But it returns no rows  The only way to make it work that I found is to also provide the time portion of the date  select   from test where date    03 19 2014 20 03 02 000

User · Answer

Test this query   SELECT   DATE chat reg date  AS is date TIME chat reg time  AS is time FROM chat WHERE chat inbox key   chat key                            ORDER BY is date DESC  is time DESC

User · Answer

use range  or  DateDiff function   select   from test   where date between  03 19 2014  and  03 19 2014 23 59 59    or   select   from test   where datediff day  date   03 19 2014     0   Other options are          If you have control over the database schema  and you don t need the time data  take it out  or  if you must keep it  add a computed column attribute that has the time portion of the date value stripped off       Alter table Test   Add DateOnly As   DateAdd day  datediff day  0  date   0   or  in more recent versions of SQL Server     Alter table Test   Add DateOnly As   Cast DateAdd day  datediff day  0  date   0  as Date   then  you can write your query as simply      select   from test  where DateOnly    03 19 2014

User · Answer

You can use this approach which truncates the time part   select   from test where convert datetime  03 19 2014  102    DATEADD dd  DATEDIFF dd  0  date   0

User · Answer

I am using MySQL 5 6 and there is a DATE function to extract only the date part from date time  So the simple solution to the question is -   select   from test where DATE date     2014-03-19     http   dev mysql com doc refman 5 6 en date-and-time-functions html

User · Answer

Try this   select   from test where Convert varchar  date 111    03 19 2014

User · Answer

This works for me for MS SQL server   select   from test where  year date    2015 and month date    10 and day date   28

User · Answer

select    cast   col1  as date   lt name of the column gt  from test where date    mm dd yyyy     col1  is name of the column with date and time  lt name of the column gt  here you can change name as desired

User · Answer

SELECT   FROM test where DATEPART year  TIMESTAMP      2018   and  DATEPART day  TIMESTAMP      16  and  DATEPART month  TIMESTAMP      11

User · Answer

-- Reverse the date format -- this false      select   from test where date    28 10 2015  -- this true      select   from test where date    2015 10 28

User · Answer

you can try this  select   from test where DATEADD dd  0  DATEDIFF dd  0  date      03 19 2014

User · Answer

select   from invoice where TRANS DATE D gt   to date    20170831115959   YYYYMMDDHH24MISS   and TRANS DATE D lt   to date    20171031115959   YYYYMMDDHH24MISS

User · Answer

Simply use this in your WHERE clause   The  SubmitDate  portion below is the column name  so insert your own    This will return only the  Year  portion of the results  omitting the mins etc   Where datepart year  SubmitDate     2017

User · Answer

select     from invoice  where TRUNC created date   lt  TRUNC to date  04-MAR-18 15 00 00   dd-mon-yy hh24 mi ss

User · Answer

Simple answer   select   from test where cast   date  as date     03 19 2014

User · Answer

use this select   from TableName where DateTimeField  gt  date   and  DateTimeField  lt  date     1

User · Answer

There is a problem with dates and languages and the way to avoid it is asking for dates with this format YYYYMMDD   This way below should be the fastest according to the link below  I checked in SQL Server 2012 and I agree with the link   select   from test where date  gt    20141903  AND date  lt  DATEADD DAY  1   20141903       Bad habits to kick   mis-handling date   range queries

User · Answer

select   from test  where date between  03 19 2014  and  03 19 2014 23 59 59    This is a realy bad answer  For two reasons   1  What happens with times like 23 59 59 700 etc  There are times larger than 23 59 59 and the next day   2  The behaviour depends on the datatype  The query behaves differently for datetime date datetime2 types   Testing with 23 59 59 999 makes it even worse because depending on the datetype you get different roundings   select convert  varchar 40  convert date         2014-03-19 23 59 59 999    select convert  varchar 40  convert datetime     2014-03-19 23 59 59 999    select convert  varchar 40  convert datetime2    2014-03-19 23 59 59 999      -- For date the value is  chopped   -- For datetime the value is rounded up to the next date   Nearest value   -- For datetime2 the value is precise

[sql-server] How to query DATETIME field using only date in Microsoft SQL Server?

Examples related to sql-server

Examples related to datetime