Handling date in SQL Server

Question

I am working on a website in asp.net. I am getting a date from a web page and then depending on the user input I want to get results from SQL Server database (using stored procedures).

Problem is that I am getting date only from UI in this format 2016-10-08 which is of type string. But in the database, I have a column which is of type datetime in this format 2016-10-08 17:38:00.000.

I am using this query to search but it does not work.

select * 
from table 
where acceptedDate like @sDate+ '%';

where sDate is input parameter for stored procedure. Please help. thanks

select * from table where convert(date,acceptedDate) >= @sDate — Dinesh Reddy Alla, Jan 23 '17 at 11:45

Zohar Peled · Accepted Answer · 2017-01-22T07:04:21.973

3

Don't pass dates as strings. Pass them as DateTime.
The .Net DateTime maps directly to SQL Server's DateTime. All you have to do is parse the string to a DateTime struct in your .Net code and pass it as a parameter to your stored procedure. To search for a specific date and ignore the Time portion of the DateTime, better use >= and < in your sql:

select * 
from table 
where acceptedDate >= @Date
AND acceptedDate < DATEADD(DAY, 1, @Date);

edited Jan 22 '17 at 07:04

answered Jan 22 '17 at 06:59

Zohar Peled

79,642
10
69
121

actually problem is i cannot find to parse input value into datetime. i am using input type=date to select date from calender. – waqar ahmed somra Jan 22 '17 at 07:22
i tried this but doesnot work String startDate = DateTime.Parse( txtstartDate.Value) – waqar ahmed somra Jan 22 '17 at 07:23
`String startDate = DateTime.Parse( txtstartDate.Value)` should not work, perhaps `DateTime startDate = DateTime.Parse( txtstartDate.Value)`. I would suggest using `TryParseExact` instead of `Parse`, so that 1. you can control the acceptable formats, and 2 you will not get an exception if the string can't be parsed. – Zohar Peled Jan 22 '17 at 07:26
[Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) – Zohar Peled Jan 22 '17 at 07:32

score 0 · Answer 2 · answered Jan 22 '17 at 08:50

If you only want compare with day level and ignoring the hours part, you can use DateDiff function. Pass d or DAY to interval parameter of DateDiff

For example:

DECLARE @sDate VARCHAR(100)='2016-10-08'
IF ISDATE(@sDate)=1
BEGIN
   select * 
   from table 
   where datediff(d,acceptedDate,@sDate)=0  --same day
END
ELSE 
 PRINT 'Invalid date format!'

Handling date in SQL Server

2 Answers2

Linked

Related