CODEWITHSUNDEEP
sql-serverdatetime
delphirules
delphirules

Reputation: 7388

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

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';

Upvotes: 115

Views: 725661

Answers (18)

Charles Bretana
Charles Bretana

Reputation: 146499

use range, or DateDiff function

 select * from test 
 where date >= '20140319' and date < '20140320'

or

 select * from test 
 where datediff(day, date, '03/19/2014') = 0

Other options are:

  1. If you have control over the database schema, and you don't need the time data, take it out.

  2. 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'

Upvotes: 166

&#214;zg&#252;r Yılmaz
&#214;zg&#252;r Yılmaz

Reputation: 1

use trunc(column).

select * from test t where trunc(t.date) = TO_DATE('2018/06/08', 'YYYY/MM/DD')

Upvotes: -4

akhil
akhil

Reputation: 21

use this

select * from TableName where DateTimeField > date() and  DateTimeField < date() + 1

Upvotes: 2

Kalidas
Kalidas

Reputation: 149

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

Upvotes: -3

mako
mako

Reputation: 78

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');

Upvotes: 1

PanSQL
PanSQL

Reputation: 1

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

Upvotes: -1

NGoyal
NGoyal

Reputation: 1

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

Upvotes: -1

Jeroen Krah
Jeroen Krah

Reputation: 129

This works for me for MS SQL server:

select * from test
where 
year(date) = 2015
and month(date) = 10
and day(date)= 28 ;

Upvotes: 12

khairollah royesh
khairollah royesh

Reputation: 1

select * from invoice where TRANS_DATE_D>= to_date  ('20170831115959','YYYYMMDDHH24MISS')
and TRANS_DATE_D<= to_date  ('20171031115959','YYYYMMDDHH24MISS');

Upvotes: -2

user8161541
user8161541

Reputation: 1

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'

Upvotes: 0

Programer_saeed
Programer_saeed

Reputation: 133

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

Upvotes: -2

Ben
Ben

Reputation: 131

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.

Upvotes: 9

Sherif Hamdy
Sherif Hamdy

Reputation: 593

-- Reverse the date format
-- this false:
    select * from test where date = '28/10/2015'
-- this true:
    select * from test where date = '2015/10/28'

Upvotes: -1

Goku__
Goku__

Reputation: 970

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

Upvotes: 28

kabcha
kabcha

Reputation: 731

Simple answer;

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

Upvotes: 73

HeLL
HeLL

Reputation: 153

you can try this

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

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460138

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)

Upvotes: 0

Amit
Amit

Reputation: 15387

Try this

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

Upvotes: 0

Related Questions