Link to home
Start Free TrialLog in
Avatar of heyday2004
heyday2004

asked on

Minimum datetime value in ASP.NET and Stored procedure question.

In one ASP.NET page, I have to use below value for the min value and max value for searching the results. The reason is the different min value and max value in ASP.NET and in SQL Server. I know it's stupid, but when I used: string MinValue = "12/12/1800"; string MaxValue = "12/12/2050"
in both asp page and the stored procedure, the search function works. But what is the better way (for sure there is) to replace the '12/12/1800', etc. in both asp page and stored procedure and will for sure WORK? Thanks.

#1: search.aspx:
public static DataTable GetSearchResults(string StartDateFrom,string StartDateTo,)
      string MinValue = "12/12/1800";
      string MaxValue = "12/12/2050";
      comm.CommandText = "GetSearchResults";

      if (StartDateFrom == "" & StartDateTo == "")
      {
          StartDateFrom = Convert.ToDateTime(MinValue).ToString();
          StartDateTo = Convert.ToDateTime(MaxValue).ToString();
      }

      param = comm.CreateParameter();
      param.ParameterName = "@StartDateFrom";
      param.Value = StartDateFrom;
      param.DbType = DbType.DateTime;
      comm.Parameters.Add(param);

...
     return GenericDataAccess.ExecuteSelectCommand(comm);        

#2  Stored procedure detail (called in #1):

...
CREATE PROCEDURE GetSearchResultsFiltered    
@StartDateFrom datetime,                  
@StartDateTo datetime
...
AS
DECLARE @MinValue DATETIME    
DECLARE @MaxValue DATETIME    
SET @MinValue = '12/12/1800'    
SET @MaxValue = '12/12/2050'    

IF (@StartDateFrom = @MinValue and @StartDateTo = @MaxValue)    
BEGIN    
SET @StartDateFrom = null    
SET @StartDateTo = null    
END    

select * from ...
where ...
AND ((@StartDateFrom is null) OR (StartDate > @StartDateFrom - 1))      
AND ((@StartDateTo is null) OR (StartDate < @StartDateTo + 1))    

...
ASKER CERTIFIED SOLUTION
Avatar of crazyman
crazyman
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial