Compare only DATE in a DATETIME field from ASP.NET code

Question

I have a table stored in SQL Server database. One of the fields of the table is of type DATETIME. I also have a ASP Calendar whose date is compared with the DATETIME filed of Database.

The problem is I don't want to compare the time. Only dates has to be compared. What is the SQL query for doing so.

This is what I used:

 SqlCommand myCommand = new SqlCommand("
SELECT AUDIT.AUDIT_DETAILS, USERS.USER_NAME, AUDIT.DATE_TIME, IP  
FROM USERS JOIN AUDIT ON (USERS.USER_ID = AUDIT.USER_ID) 
WHERE USERS.USERS_NAME LIKE '%"+TextBox1.Text+"%' AND CONVERT(VARCHAR(10),AUDIT.DATE_TIME,110) like "+Calendar1.SelectedDate.Date+"'%'", conn);

I'm getting correct output in the SQL Server explorer but when run from browser through asp.net the query result is empty

Can you please show in which format you are testing in SQL Server explorer and in which format is your Calendar1.SelectedDate.Date? Maybe the problem is date format. — Chuck Norris
– Chuck Norris, Commented Apr 23, 2012 at 7:20
I too think so. I have actually not specified any format. How do i make them both in same format. — Rohit Vipin Mathews
– Rohit Vipin Mathews, Commented Apr 23, 2012 at 7:22
You can put Calendar1.SelectedDate.Date in what format SQL needs like this Calendar1.SelectedDate.Date.ToString("format") where instead of format you must write format that you need like dd/MM/yyyy or yyyy/MM/dd — Chuck Norris
– Chuck Norris, Commented Apr 23, 2012 at 7:29

Surya Pratap · Accepted Answer · 2012-04-23 09:00:23Z

3

Try modifying your query as below

  SqlCommand myCommand = new SqlCommand("SELECT AUDIT.AUDIT_DETAILS, USERS.USER_NAME, AUDIT.DATE_TIME, IP  
  FROM USERS JOIN AUDIT ON (USERS.USER_ID = AUDIT.USER_ID) 
  WHERE USERS.USERS_NAME LIKE '%"+TextBox1.Text+"%' AND
  CONVERT(VARCHAR(10),AUDIT.DATE_TIME,106) ==    CONVERT(VARCHAR(10),CAST('"+Calendar1.SelectedDate.Date+"' as DATETIME),106)", conn);

answered Apr 23, 2012 at 9:00

Surya Pratap

4959 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chuck Norris · Accepted Answer · 2012-04-23 07:01:37Z

2

You can get only date from Datetime in SQL like this

CONVERT(DATE,GETDATE())

And after that you can compare to that value.

answered Apr 23, 2012 at 7:01

Chuck Norris

15.2k15 gold badges96 silver badges128 bronze badges

Collectives™ on Stack Overflow

Compare only DATE in a DATETIME field from ASP.NET code

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related