locked
passing null datetime into sproc RRS feed

  • Question

  • User-2012457684 posted

    i have a sproc with 2 datetime parameters that are nullable.   I have a C# method to call that sproc and get the results.  It works fine when running it in SQL server.   When I run it in the page, I get an error converting data type datetime2 to datetime.     The data type in SQL Server that I am using is datetime not datetime2

    Here is the sproc  

    CREATE PROCEDURE [dbo].[ApplicantList]
    
    @LocationID int,
    @FolderID int,
    @JobID int,
    @Archive int,
    @AdminID nvarchar(15),
    @AppArchive tinyint,
    @StartDate datetime = NULL,
    @EndDate datetime = NULL
    
    AS
    
    SET NOCOUNT ON
    
    DECLARE @RoleID int
    
    SELECT 
    	@RoleID = RoleID 
    FROM
    	dbo.AdminUsers
    WHERE
    	AdminID = @AdminID 
    
     
    IF @RoleID < 4
    	BEGIN
    		SELECT DISTINCT
    			JA.ApplicantID,
    			JA.ApplicationID,
    			JA.QQViewed,
    			JA.AppViewed,
    			JA.CVViewed,
    			JA.SSViewed,
    			JA.RptViewed,
    			A.PrescreenScore,
    			A.ApplyDate,
    			A.GivenName,
    			A.FamilyName,
    			J.JobTitle,
    			L.AreaName
    		FROM
    			dbo.Applicant JA INNER JOIN
    			dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.Locations L ON JA.LocationID = L.LocationID
    		WHERE
    			(JA.LocationID = @LocationID OR @LocationID = 9999)
    			AND (JA.FolderID = @FolderID OR @FolderID = 99)
    			AND (A.JobID = @JobID OR (@JobID = 9999  AND (@Archive = 2 OR J.Archived = CAST(@Archive AS bit))))
    			AND (JA.Archived = @AppArchive OR @AppArchive = 2)
    			AND A.ApplyDate >= CASE WHEN @StartDate IS NULL THEN A.ApplyDate ELSE @StartDate END
    			AND A.ApplyDate < CASE WHEN @EndDate IS NULL THEN DATEADD(d,1,A.ApplyDate) ELSE DATEADD(d,1,@EndDate) END
    	END
    IF @RoleID = 4 -- Division Mgr
    	BEGIN
    		SELECT DISTINCT
    			JA.ApplicantID,
    			JA.ApplicationID,
    			JA.QQViewed,
    			JA.AppViewed,
    			JA.CVViewed,
    			JA.SSViewed,
    			JA.RptViewed,
    			A.PrescreenScore,
    			A.ApplyDate,
    			A.GivenName,
    			A.FamilyName,
    			J.JobTitle,
    			L.AreaName
    		FROM
    			dbo.Applicant JA INNER JOIN
    			dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN
    			dbo.Jobs J ON A.JobID = J.JobID INNER JOIN
    			dbo.Locations L ON JA.LocationID = L.LocationID
    		WHERE
    			(JA.LocationID = @LocationID OR @LocationID = 9999)
    			AND (JA.FolderID = @FolderID OR @FolderID = 99)
    			AND (A.JobID = @JobID OR (@JobID = 9999  AND (@Archive = 2 OR J.Archived = CAST(@Archive AS bit))))
    			AND J.DivisionID IN (SELECT DivisionID FROM dbo.DivisionManager WHERE AdminID = @AdminID)
    			AND (JA.Archived = @AppArchive OR @AppArchive = 2)
    			AND A.ApplyDate >= CASE WHEN @StartDate IS NULL THEN A.ApplyDate ELSE @StartDate END
    			AND A.ApplyDate < CASE WHEN @EndDate IS NULL THEN DATEADD(d,1,A.ApplyDate) ELSE DATEADD(d,1,@EndDate) END
    	END

    here is the method in the class

     public class ApplicantListing
        {
            NewAMSEntities db = new NewAMSEntities();
            public int Archive { get; set; }    
            public int JobID { get; set; }
            public int FolderID { get; set; }
            public int LocationID { get; set; } 
            public string AdminID { get; set; }
            public byte AppArchive {get; set; }
            public Nullable<DateTime> StartDate { get; set; }
            public Nullable<DateTime> EndDate { get; set; }
    
            public List<ApplicantList_Result> ListOfApplicants()
            {
                return db.ApplicantList(LocationID, FolderID, JobID, Archive, AdminID, AppArchive, StartDate, EndDate).ToList();
            }
        }

    and here is my controller

    [HttpPost]
    public ActionResult Index(int Archive, int JobID, int LocationID, int FolderID, string AdminID, byte AppArchive, string StartDate, string EndDate, ApplicantListing App)
    {
        ViewData["AdminID"] = System.Web.HttpContext.Current.Session["AdminID"] as String;
        if (ViewData["AdminID"] != null)
        {
                  
            ViewBag.FirstLoad = false;
            ViewBag.AdminID = AdminID;
            App.AdminID = AdminID;
            var Jobs = App.listJobs();
            ViewBag.JobID = Jobs;
    
            var folderlist = App.listFolders();
            ViewBag.FolderID = folderlist;
    
            var loclist = App.listLocations();
            ViewBag.LocationID = loclist;
    
            switch (FolderID)
            {
                case 1:
                case 3:
                case 4:
                case 5:
                case 6:
                    ViewBag.Moveable = true;
                    break;
                default:
                    ViewBag.Moveable = false;
                    break;
            }
    
                    
            App.Archive = Archive;
            App.JobID = JobID;
            App.FolderID = FolderID;
            App.LocationID = LocationID;
            App.AdminID = AdminID;
            App.AppArchive = AppArchive;
            if(StartDate != "")
            {
                App.StartDate = Convert.ToDateTime(StartDate);
            }
            if(EndDate != "")
            {
                App.EndDate = Convert.ToDateTime(EndDate);
            }
            List<ApplicantList_Result> applicantList = App.ListOfApplicants();
            ViewBag.Applicants = applicantList;
            List<ListFoldersAvailabe_Result> AvaFolders = App.AvailableFolders();
            List<ListAvailableReasons_Result> AvaReasons = App.AvailableReasons();
            List<ListAvailableDispositions_Result> AvaDispositions = App.AvailableDispostions();
            ViewBag.AvaFolders = AvaFolders;
            ViewBag.Reasons = AvaReasons;
            ViewBag.Dispositions = AvaDispositions;
            return View();
        }
        return View("LoggedOut");
    }

    What do i have wrong?    How do I use this when the date fields are allowed to be null in the procedure?


     

     

    Wednesday, July 18, 2018 8:41 PM

All replies

  • User1120430333 posted


    From what I recall, if a table column can be a null value, then you simply do not address the column that can be a null value when doing an insert as an example. 

    Thursday, July 19, 2018 3:16 AM
  • User1520731567 posted

    Hi mj1223,

    When I run it in the page, I get an error converting data type datetime2 to datetime.     The data type in SQL Server that I am using is datetime not datetime2

    This can happen if you do not assign a value to a DateTime field when the field does not accept NULL values.

    On SQL Server, the date range supported is quite different.

    DATETIME supports 1753/1/1 to "eternity" (9999/12/31), while DATETIME2 supports 0001/1/1 through eternity.

    Try to add the code below:

    [Column(TypeName = "DateTime2")]

    Or,there are some other solutions in this link,you could refer to it:

    https://stackoverflow.com/questions/1331779/conversion-of-a-datetime2-data-type-to-a-datetime-data-type-results-out-of-range

    Best Regards.

    Yuki Tao

    Thursday, July 19, 2018 9:30 AM
  • User-2012457684 posted

    Try to add the code below:

    [Column(TypeName = "DateTime2")]

     add it where?   is this C# code ?

    Thursday, July 19, 2018 2:43 PM
  • User1520731567 posted

    Hi mj1223,

    Just like:

     [Column(TypeName = "datetime2")]
     public DateTime? NullableDateTimePropUtc { get; set; }

    Add this annotation to the related  field in model.

    And More details,you could refer to this link:

    https://stackoverflow.com/questions/1331779/conversion-of-a-datetime2-data-type-to-a-datetime-data-type-results-out-of-range

    Best Regards.

    Yuki Tao

    Friday, July 20, 2018 1:43 AM