Vfrdtyky

Simple answer;

select * from test where cast ([date] as date) = '03/19/2014';

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

Try this

 select * from test where Convert(varchar, date,111)= '03/19/2014'

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.

you can try this

select * from test where DATEADD(dd, 0, DATEDIFF(dd, 0, date)) = '03/19/2014';

This works for me for MS SQL server:

select * from test

where 

year(date) = 2015

and month(date) = 10

and day(date)= 28 ;

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)

-- Reverse the date format

-- this false:

    select * from test where date = '28/10/2015'

-- this true:

    select * from test where date = '2015/10/28'

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

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'

select *, cast ([col1] as date) <name of the column> from test where date = 'mm/dd/yyyy'

"col1" is name of the column with date and time

<name of the column> here you can change name as desired

select *

  from invoice

 where TRUNC(created_date) <=TRUNC(to_date('04-MAR-18 15:00:00','dd-mon-yy hh24:mi:ss'));

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 >= '20141903' AND date < DATEADD(DAY, 1, '20141903');

• Bad habits to kick : mis-handling date / range queries

select * from invoice where TRANS_DATE_D>= to_date  ('20170831115959','YYYYMMDDHH24MISS')

and TRANS_DATE_D<= to_date  ('20171031115959','YYYYMMDDHH24MISS');

SELECT * FROM test where DATEPART(year,[TIMESTAMP]) = '2018'  and  DATEPART(day,[TIMESTAMP]) = '16' and  DATEPART(month,[TIMESTAMP]) = '11'