Stored procedure, date and time

Question

I have a search form on a website where I have to search in all or in one database column. I have the following stored procedure and a problem with datetime. How to make it null?

I have a problem with this both in the stored procedure and in the code with C # code.

I hope you help me!

ALTER PROCEDURE [dbo].[SearchPostit] ( 
@message varchar(1000)='', 
@writer varchar(50)='', 
@mailto varchar(100)='', 
@date Datetime ) AS 
SELECT  P.Message AS Postit, 
        P.Mailto AS 'Mailad till', 
        P.[Date] AS Datum , 
        U.UserName AS Användare
FROM PostIt P  LEFT OUTER JOIN 
     [User] U ON P.UserId=U.UserId

WHERE P.message LIKE '%'+@message+'%' AND P.Mailto LIKE '%'+@mailto+'%' 
AND P.Date LIKE '%'+@date+'%' AND U.UserName LIKE '%'+@writer+'%'
GROUP BY P.Message, P.Mailto, P.[Date], U.UserName

This is my code when you click the search button. But I got an error if there is no date in the text box ... Postit p = new Postit (); DateTime dt = DateTime.Parse (TextBoxDate.Text); p.SearchPostit (TextBoxMessage.Text, TextBoxWriter.Text, TextBoxMailto.Text, dt);

Answer 1

DateTime.Parse() . TextBox - "", , , . DateTime.TryParse(), , TextBox . DateTime , .. 01-Jan-0001. , , - , , /.

sp.

DateTime dt=DateTime.Parse(TextBoxDate.Text) // fails if text is empty

DateTime

DateTime dt = new DateTime();
dt = DateTime.Parse(TextBoxDate.Text)   // would fail if text is empty
DateTime.TryParse(TextBoxDate.Text, out dt) // would NOT fail if text is empty string

:

DateTime dt;
DateTime.TryParse(TextBoxDate.Text, out dt);

, sp, , 01-JAN-0001, .

DateTime sp. , varchar SQL DateTime sp.

---

SP

        ALTER PROCEDURE [dbo].[SearchPostit] ( 
@message varchar(1000), 
@writer varchar(50), 
@mailto varchar(100), 
@date Datetime ) AS

DECLARE @msg varchar(1002)
DECLARE @wrt varchar(52)
DECLARE @mlt varchar(102)
DECLARE @dte DateTime

IF (@message IS NULL)
BEGIN
    SET @msg = null
END
ELSE
    SET @msg = "%"+@message+"%"

    -- similar for mailto, writer values, there is probaby a cleaner way to do this though

IF (@date is not null)
BEGIN
    SET @ dte = CONVERT(Datetime, @date, 101) 
END 



SELECT  P.Message AS Postit, 
        P.Mailto AS "Mailad till", 
        P.[Date] AS Datum , 
        U.UserName AS Användare
FROM PostIt P  
LEFT OUTER JOIN 
     [User] U ON P.UserId=U.UserId
WHERE 
    P.message   LIKE COALESCE(@msg, P.Message)  AND 
    P.Mailto    LIKE COALESCE(@lt, P.Mailto)    AND 
    P.Date      LIKE @date                      AND
    U.UserName  LIKE COALESCE(@wrt, P.UserName) 
GROUP BY P.Message, P.Mailto, P.[Date], U.UserName

Answer 2

# DateTime . - :

DateTime parsedDateValue;
DateTime? dateValue = null;

If (DateTime.TryParse(DateTextBox.Text, out parsedDateValue)
   dateValue = parsedDateValue;

DateTime out TryParse. DbCommand DbParameters DateTime? .

, , : P. [Date] Like '%' + @date + '%'. (@Date Is Null P. [Date] = @Date), @Date , , P. [Date]. , .NET - (.NET , SQL Server DateTime 300 ). , - :

( 
@Date Is Null Or 
    ( P.[Date] >= Cast(DateDiff(d, 0, @Date) As DateTime)
    And 
    P.[Date] < Cast(DateDiff(d, 0, @Date) + 1 As DateTime)
    )
)

, @Date. , . , . , @Date .

Answer 3

where -

WHERE   P.message LIKE '%'+@message+'%' 
AND     P.Mailto LIKE '%'+@mailto+'%'  
AND     (
                @date IS NULL 
            OR  P.Date = @date
        )
AND     U.UserName LIKE '%'+@writer+'%' 

Answer 4

, - (P.Date LIKE.... OR @date IS NULL). LIKE BETWEEN > =/< = DATETIME. , , AND U.UserName LIKE... LEFT JOIN INNER JOIN, WHERE LIKE .

Answer 5

I assign null by default, and then process null case in the where clause, which should handle the time when you are not specifying a date.

ALTER PROCEDURE [dbo].[SearchPostit] (
@message varchar(1000)='',
@writer varchar(50)='',
@mailto varchar(100)='',
@date Datetime = null ) AS
SELECT  P.Message AS Postit,
        P.Mailto AS 'Mailad till',
        P.[Date] AS Datum ,
        U.UserName AS Användare
FROM PostIt P  LEFT OUTER JOIN
     [User] U ON P.UserId=U.UserId

WHERE
      P.message LIKE '%'+@message+'%' AND
      P.Mailto LIKE '%'+@mailto+'%' AND
      ((@date is null) or (P.Date LIKE '%'+@date+'%')) AND
      U.UserName LIKE '%'+@writer+'%'
GROUP BY P.Message, P.Mailto, P.[Date], U.UserName