0

I have a column Expiry_Date with date and time values in table1.

I am sending a date value from the front end using C#.

I write the query to get some information if where condition is true.

For ex: in table1, Expiry_Date has the value 2016-03-11 23:59:59.000

select USER_NAME, USER_EMAIL 
from table1 
where Expiry_Date = '2016-03-12';

When I execute this, I am not getting any results. Actually the table has the data for this date, due to not having time part in supplied date, it's not matching with Expiry_Date.

So please let me know how can we do this without passing time from front end.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sam
  • 95
  • 1
  • 2
  • 13
  • 1
    Look at `CAST(Expiry_Date AS DATE)` . It should convert your DateTime to Date. – Dobz Mar 12 '16 at 09:03
  • Possible duplicate of [Compare two DATETIME only by date not time in SQL Server 2008](http://stackoverflow.com/questions/1843395/compare-two-datetime-only-by-date-not-time-in-sql-server-2008) – White Feather Mar 12 '16 at 09:33

3 Answers3

1

You can cast Expiry_Date to Date in your where clause...

select USER_NAME, USER_EMAIL 
from table1 
where (CAST Expiry_Date AS DATE) = '2016-03-12';

This should return all the record with expiry date '2016-03-12' of any time.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Teddy
  • 304
  • 5
  • 17
1

don't use cast() or convert() on the column in where clause. It will give not give you good performance. Use this instead

where Expiry_Date >= '2016-03-12'
and   Expiry_Date < DATEADD(DAY, 1, '2016-03-12')
Squirrel
  • 23,507
  • 4
  • 34
  • 32
0

This answer to your question:

Compare two DATETIME only by date not time in SQL Server 2008

Sorry about my previous answer that referred to Mysql not SQL Server.

Regards

Community
  • 1
  • 1
White Feather
  • 2,733
  • 1
  • 15
  • 21