Assign values to datetime type variables in SQL

 

Question: Use the stored procedure to count the number of books borrowing users in a certain period of time. Requirement: If the start date is not specified, the previous month is used as the start date. If no end date is specified, it takes the current day as the deadline.

 1   If   Exists ( Select   *   From Sysobjects Where Name =   '  Usp_searchlendinfo  ' )
 2   Drop  Procedure Usp_searchlendinfo
 3   Go 
 4   Create   Procedure Usp_searchlendinfo
 5   @ Startdate   Datetime   =   Null ,
 6   @ Enddate   Datetime   = Null 
 7   As 
 8   Set Nocount On 
 9   Declare   @ Sdate   Datetime   =   Dateadd ( Month , -  1 ,Getdate ()) --  Obtain the time of the previous month  
  10   Declare   @ Edate   Datetime   =   Getdate () --  Get current time  
  11  
 12   -- -When the user does not enter the start time and end time, both are null.  
  13   If ( @ Startdate   Is   Null   And   @ Enddate   Is   Null )
 14   Begin 
 15       Set   @ Startdate  =   @ Sdate 
 16       Set   @ Enddate   =   @ Edate 
 17   End 
 18   Else   If ( @ Startdate   Is   Null )
 19  Set   @ Startdate   =   @ Sdate     
 20  
 21   Else   If ( @ Enddate   Is   Null )
 22   Set   @ Enddate   =   @ Edate 
23  
 24   Print   '  The following is from  '  +  Convert ( Varchar ( 15 ), @ Startdate ) +  '  To  '  + Convert ( Varchar ( 15 ), @ Enddate ) +  '  Books borrow information  ' 
 25       Select Bname book name, Count ( * ) Borrow quantity From Borrow Inner  Join Book
 26       On Book. Bid = Borrow. Bid Where Lenddate >   @ Startdate   And Willdate <  @ Enddate   Group   By Bname
 27   Go 
28   Exec Usp_searchlendinfo '  2012-2-9  ' , '  2012-4-9  '