Database Insert is not returning NULL to a datetime field

Home Forum Integrating PerfectForms Database Insert is not returning NULL to a datetime field

This topic contains 0 replies, has 0 voices, and was last updated by  ijobling 9 years, 1 month ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #317

    ijobling
    Participant

    Inserting or updating data into a database table with a field of type “datetime”, where no data is updated from the form expectation is that NULL should be stored in the database table.

    However when the database is queried later, with a SELECT action it returns, but 1900-01-01 00:00:00.000

    This is ‘expected’ behaviour of SQL The dynamic query will insert default value for date and this can only be handled with a stored procedure where you check for null or empty string and set it with their own value or NULL. This limitation is not because of Perfectforms but because of how MS-Sql works.

    See below the example for the sql stored procedure example:

    ALTER PROCEDURE InsertDateTimeNullTest

    — Add the parameters for the stored procedure here

    @FirstName varchar(50),

    @LastName varchar(50),

    @DOB datetime = null

    AS

    BEGIN

    IF @DOB = ” OR @DOB IS NULL

    BEGIN

    SET @DOB = NULL

    END

    — Insert statements for procedure here

    INSERT INTO [DateTimeNullInsertTest] (

    [FirstName],

    [LastName],

    [DOB])

    VALUES (

    @FirstName,

    @LastName,

    @DOB)

    END

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.