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, Apr 23 '12 at 07:20
I too think so. I have actually not specified any format. How do i make them both in same format. — Rohit Vipin Mathews, Apr 23 '12 at 07: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, Apr 23 '12 at 07:29
This worked: `SELECT AUDIT.AUDIT_DETAILS, USERS.USERS_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,105) LIKE '%" + Calendar1.SelectedDate.ToString("d-MM-yyyy")` — Rohit Vipin Mathews, Apr 23 '12 at 09:39

score 3 · Answer 1 · answered Apr 23 '12 at 09:00

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

score 2 · Accepted Answer · answered Apr 23 '12 at 07:01

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 '12 at 07:01

Chuck Norris

15,207
15
92
123

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

2 Answers2