Skip to main content
S
shearak
Inspiring
May 14, 2009
Answered

Null value in Datetime field

  • May 14, 2009
  • 2 replies
  • 1424 views

I have a form that inserts data, but the date fields are getting a default value (1/1/1900 12:00:00 AM) even though I don't insert a value for that field. How would I insert a NULL value in the datatime fields in MSSQL.instead of the default value (1/1/1900 12:00:00 AM) that gets inserted?

Here is my insert statement.

<CFLOCK type="exclusive" timeout="10">
    <CFQUERY name="InsertWorkflow"
          datasource="#request.app.DSN#"
          username="#request.app.bsruser#"
          password="#request.app.bsrPassword#">
        INSERT INTO #request.app.DB#.v_NAR_WorkFlowLog
        ([SatApp_ID]
           ,[User_ReqApproval_Logon]
           ,[User_ReqApproval_Date]
           ,[Admin_ReqProcessed_Logon]
           ,[Admin_ReqProcessed]
           ,[Prelim_Approval_Level]
           ,[Prelim_Approval_Date]
           ,[Prelim_Approval_Explain]
           ,[Req_Withdrawn_Date]
           ,[Req_Withdrawn_Explain]
           ,[Req_Denied_Date]
           ,[Req_Denied_Explain]
           ,[User_Req_FinalAppl_Date]
           ,[Admin_FinalAppl_Date]
           ,[DateLastUpdated]
           ,[UpdatedBy])           
        VALUES
           (#SatApp_ID#
           ,'#User_ReqApproval_Logon#'
           ,'#User_ReqApproval_Date#'
           ,'#Admin_ReqProcessed_Logon#'
           ,'#Admin_ReqProcessed#'
           ,'#Prelim_Approval_Level#'
           ,'#Prelim_Approval_Date#'
           ,'#Prelim_Approval_Explain#'
           ,'#Req_Withdrawn_Date#'
           ,'#Req_Withdrawn_Explain#'
           ,'#Req_Denied_Date#'
           ,'#Req_Denied_Explain#'
           ,'#User_Req_FinalAppl_Date#'
           ,'#Admin_FinalAppl_Date#'
           ,'#DateLastUpdated#'
           ,'#UpdatedBy#')                              
    </CFQUERY>
</CFLOCK>

Thanks,

Shearak

    This topic has been closed for replies.
    Correct answer tgruen

    Would CFQueryParam work? For example:

    <cfqueryparam cfsqltype="CF_SQL_DATE" value="#DateLastUpdated#" null = "#YesNoFormat(NOT Len(Trim(DateLastUpdated)))#">

    So, using your given example, your insert would be:

    <CFLOCK type="exclusive" timeout="10">
        <CFQUERY name="InsertWorkflow"
              datasource="#request.app.DSN#"
              username="#request.app.bsruser#"
              password="#request.app.bsrPassword#">
            INSERT INTO #request.app.DB#.v_NAR_WorkFlowLog
            ([SatApp_ID]
               ,[User_ReqApproval_Logon]
               ,[User_ReqApproval_Date]
               ,[Admin_ReqProcessed_Logon]
               ,[Admin_ReqProcessed]
               ,[Prelim_Approval_Level]
               ,[Prelim_Approval_Date]
               ,[Prelim_Approval_Explain]
               ,[Req_Withdrawn_Date]
               ,[Req_Withdrawn_Explain]
               ,[Req_Denied_Date]
               ,[Req_Denied_Explain]
               ,[User_Req_FinalAppl_Date]
               ,[Admin_FinalAppl_Date]
               ,[DateLastUpdated]
               ,[UpdatedBy])           
            VALUES
               (#SatApp_ID#
               ,'#User_ReqApproval_Logon#'
               ,'#User_ReqApproval_Date#'
               ,'#Admin_ReqProcessed_Logon#'
               ,'#Admin_ReqProcessed#'
               ,'#Prelim_Approval_Level#'
               ,'#Prelim_Approval_Date#'
               ,'#Prelim_Approval_Explain#'
               ,'#Req_Withdrawn_Date#'
               ,'#Req_Withdrawn_Explain#'
               ,'#Req_Denied_Date#'
               ,'#Req_Denied_Explain#'
               ,'#User_Req_FinalAppl_Date#'
               ,'#Admin_FinalAppl_Date#'
               ,<cfqueryparam cfsqltype="CF_SQL_DATE" value="#DateLastUpdated#" null = "#YesNoFormat(NOT Len(Trim(DateLastUpdated)))#">           ,'#UpdatedBy#')                              
        </CFQUERY>
    </CFLOCK>

    2 replies

    ilssac
    ilssac
    Inspiring
    May 14, 2009

    Which field is getting the default value?

    Is the field nullable in the database?

    Does the database have a default value configured?

    If the field is nullable and does not have a default value in the database and you do not set any value in the query, i.e. completely leave that field out of the query, you should get a null value.

    T
    tgruenCorrect answer
    Inspiring
    May 14, 2009

    Would CFQueryParam work? For example:

    <cfqueryparam cfsqltype="CF_SQL_DATE" value="#DateLastUpdated#" null = "#YesNoFormat(NOT Len(Trim(DateLastUpdated)))#">

    So, using your given example, your insert would be:

    <CFLOCK type="exclusive" timeout="10">
        <CFQUERY name="InsertWorkflow"
              datasource="#request.app.DSN#"
              username="#request.app.bsruser#"
              password="#request.app.bsrPassword#">
            INSERT INTO #request.app.DB#.v_NAR_WorkFlowLog
            ([SatApp_ID]
               ,[User_ReqApproval_Logon]
               ,[User_ReqApproval_Date]
               ,[Admin_ReqProcessed_Logon]
               ,[Admin_ReqProcessed]
               ,[Prelim_Approval_Level]
               ,[Prelim_Approval_Date]
               ,[Prelim_Approval_Explain]
               ,[Req_Withdrawn_Date]
               ,[Req_Withdrawn_Explain]
               ,[Req_Denied_Date]
               ,[Req_Denied_Explain]
               ,[User_Req_FinalAppl_Date]
               ,[Admin_FinalAppl_Date]
               ,[DateLastUpdated]
               ,[UpdatedBy])           
            VALUES
               (#SatApp_ID#
               ,'#User_ReqApproval_Logon#'
               ,'#User_ReqApproval_Date#'
               ,'#Admin_ReqProcessed_Logon#'
               ,'#Admin_ReqProcessed#'
               ,'#Prelim_Approval_Level#'
               ,'#Prelim_Approval_Date#'
               ,'#Prelim_Approval_Explain#'
               ,'#Req_Withdrawn_Date#'
               ,'#Req_Withdrawn_Explain#'
               ,'#Req_Denied_Date#'
               ,'#Req_Denied_Explain#'
               ,'#User_Req_FinalAppl_Date#'
               ,'#Admin_FinalAppl_Date#'
               ,<cfqueryparam cfsqltype="CF_SQL_DATE" value="#DateLastUpdated#" null = "#YesNoFormat(NOT Len(Trim(DateLastUpdated)))#">           ,'#UpdatedBy#')                              
        </CFQUERY>
    </CFLOCK>

    S
    shearakAuthor
    Inspiring
    May 14, 2009

    Thank you! Your method worked great. Now I have NULL values instead of the default value MSSQL Server inserts.

    <cfqueryparam cfsqltype="CF_SQL_DATE" value="#DateLastUpdated#" null = "#YesNoFormat(NOT Len(Trim(DateLastUpdated)))#">

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices