CODEWITHSUNDEEP
c#t-sqlstored-procedures
Learning Curve
Learning Curve

Reputation: 1569

Date comparison in where clause of sql stored procedure

Could please anyone tell me how I can do the date comparison in where clause of a sql stored procedure.

I have a provided a text box filter to the user where user can enter date in any format (e.g. 13/09/2013, 13092013, 13-Sep-2013, 130913). The DB table has datetime type column on which I want to apply this filter to.

Here I have 3 questions

  1. Do I have to convert it to some specific date only type in my c# code first. IF yes than does the date format matter?
  2. How to check if the date type parameter is not null or empty?
  3. How I will compare the above date only type (from c# code) with datetime column of my DB table?

A code example will really be appreciated.

Kind Regards

Upvotes: 1

Views: 1165

Answers (1)

Kaf
Kaf

Reputation: 33839

Answers:

(1). You should force user to enter in a CERTAIN FORMAT. If you don't know the entering format, how do you know if 12-11-2013 12th of November or 11th of December? This is just a one example.

If you know the user entering format, you can either convert it to a date in the code or pass as a string in ISO FORMAT (yyyymmdd) into the stored procedure.

First method would be better as if the user input is not a valid date then you could stop calling the stored procedure.

(2). Your code behind receives the user input as a string. So you can check it in code behind like;

 bool isNull = string.IsNullOrWhiteSpace(userEnteredDate);

(3). in your stored procedure

 --Rest of the query
 WHERE Convert(date, yourdateColumn) = @yourDateParameter

Upvotes: 2

Related Questions