none
insert a null value into a date field

    Question

  •  When I try to insert "NULL" into the date field it turns into 1/1/1990   I want to set the date field back to nothing

    	insert dbo.inventory (
    			[PURCHASE ORDER NO]
               ,[REFNO]
               ,[COFFEE_TYP]
               ,[CAFETYPE]
               ,[NO_BGS_AVL]
               ,[MARKS]
               ,[MARKSpredecaf]
               ,[MKSblend]
               ,[NO_BGS_IN]
               ,[RPM_CRGO_N]
               ,[DATE_IN]
               ,[NET_WT]
               ,[COST]
               ,[FINAL WHSE-IN COST]
               ,[FinalWhseInCostMendes]
               ,[FinalWhseInArchive]
               ,[FINAL WHSE-IN DIFF]
               ,[FIXATION BASIS]
               ,[ASK]
               ,[POSITION]
               ,[FINAL POSITION]
               ,[V_1_O2_D_3]
               ,[NOTES]
               ,[PRINTLABEL]
               ,[consign(y/n)]
               ,[SELLER]
               ,[SELLER AGENT]
               ,[INTERMEDIARY]
               ,[PAYMENT TERMS]
               ,[PURCHASE REMARKS]
               ,[TENDERED]
               ,[TENDER DATE]
               ,[SALE DATE]
               ,[SELLER NUMBER]
               ,[WEIGHTS]
               ,[DATE PURCHASE PAID]
               ,[PURCHASE DUE DATE]
               ,[PRICE TO BE FIXED?]
               ,[DATE FIXED]
               ,[FUTURES BASIS]
               ,[DIFFRTL PRICE]
               ,[Differentl number]
               ,[PriceDescriptor]
               ,[BBH START UP]
               ,[ADJUSTED NET INSTOCK]
               ,[FORWARD PURCH]
               ,[PAID]
               ,[D/O ALLOCATION]
               ,[WAREHSE RCPT #]
               ,[B/L DATE]
               ,[NOLABELS]
               ,[EXWHS/FOB/ETC]
               ,[H-INDEX]
               ,[PROFORMA PAYMENT]
               ,[PROFORMA DATE]
               ,[TITLE DATE]
               ,[SAS]
               ,[RCD SHIP ADVICE]
               ,[SHIP COMMENTS]
               ,[MIXED CNTR?]
               ,[No SLACKS]
               ,[READY 2 SHIP MIX CNTR]
               ,[ROYAL SF REF NO]
               ,[REJECTED?]
               ,[DATE/DISPLAY ORDER]
               ,[POLABELS]
               ,[PTBF BY ROYAL]
               ,[PTBF BY SHIPPER]
               ,[APPROVED?]
               ,[APPROVAL DATE INFO]
               ,[MISC(Y/N)]
               ,[TOLL DECAF?]
               ,[NOLABELS1]
               ,[DESTINATION]
               ,[MISC HEDGE LABEL]
               ,[FAIR TRADE]
               ,[SHADE GROWN]
               ,[RAINFOREST ALLIANCE]
               ,[SMBC]
               ,[OCIA PRODUCER #]
               ,[OCIA ORIGINAL TRANS CERT#]
               ,[OCIA CERTIFIED?]
               ,[SLACK INDICATOR]
               ,[VESSEL]
               ,[T/S VESSEL]
               ,[VOYAGE NUMBER]
               ,[T/S VOYAGE NUMBER]
               ,[BILL OF LAIDING NUMBER]
               ,[BILL OF LAIDING DATE]
               ,[PORT OF LOADING ID]
               ,[CONTAINER NUMBER]
               ,[VESSEL ETA]
               ,[ARRIVAL PIER ID]
               ,[STEAMSHIP LINE ID]
               ,[CUSTOMS ENTRY NUMBER]
               ,[INBOUND WEIGHING]
               ,[BAG TARE]
               ,[FDA RELEASE DATE]
               ,[CUSTOMS RELEASE DATE]
               ,[Phytosanitary Cert?]
               ,[Shipper Weight cert?]
               ,[Cert of Origin?]
               ,[Fumigation Cert?]
               ,[Packing List?]
               ,[Draying Trucker ID]
               ,[MID]
               ,[YEAR ID]
               ,[FINAL DESTN WHSE]
               ,[HOTLIST]
               ,[FLOidno]
               ,[TEST MARKS]
               ,[FUTURESCOMPOSITEinvtry]
               ,[HEDGEadjustment]
               ,[FINAL WHSE-IN COSTadj]
               ,[RFAbuyTransaction]
               ,[WEBinvisible]
               ,[InventoryTransOrigRef]
               ,[NonSKUpricingFlag]
               ,[GrainProDate]
               ,[FOBExhseDescriptor]
               ,[FuturesNameDescriptor]
               ,[FixedPriceOptionCoop]
               ,[SKUaverageprice]
               ,[FuturesHedgeID]
               ,[WhseLocator]
               ,[PaymentTermInvID]
               ,[FOBtoExWhseValueTemp]
               ,[Last_UpdateBy]
               ,[PurchasedDIF_FIX]
               ,[shipterm]
               ,[OrgFixationBase]
               ,[orgdatefixed]
               ,[Orgdateinstore]
               ,[GammaProc]
               ,[SupplierCom]
               ,[RainforestCom]
               ,[OrganicCom]
               ,[GTCPrice]
               ,[GTCFilled]
               ,[GTCDateFilled]
               ,[GTCNotes]
               ,[GTCContract]
               ,[IntermediaryCom]
               ,[FairTradeMinYes]
               ,[FixationCurrentLevel]
               ,[ComOtherName]
               ,[ComOtherLevel]
               ,[MetricTonPrice]
               ,[MetricTonDif]
               ,[MetricTonFix]
               ,[TransMockSaleId])
        
    	select     @ponumber/*[PURCHASE ORDER NO]*/
               ,[REFNO]
               ,[COFFEE_TYP]
               ,[CAFETYPE]
               ,[NO_BGS_AVL]
               ,[MARKS]
               ,[MARKSpredecaf]
               ,[MKSblend]
               ,@orgnobags /*[NO_BGS_IN]*/
               ,[RPM_CRGO_N]
               ,NULL /*[DATE_IN]*/
               ,[NET_WT]
               ,@finalwhseincost/*[COST]*/
               ,[FINAL WHSE-IN COST]
               ,[FinalWhseInCostMendes]
               ,[FinalWhseInArchive]
               ,[FINAL WHSE-IN DIFF]
               ,[FIXATION BASIS]
               ,[ASK]
               ,[POSITION]
               ,@datedisplay /*[FINAL POSITION]*/
               ,[V_1_O2_D_3]
               ,[NOTES]
               ,[PRINTLABEL]
               ,[consign(y/n)]
               ,[SELLER]
               ,[SELLER AGENT]
               ,[INTERMEDIARY]
               ,[PAYMENT TERMS]
               ,[PURCHASE REMARKS]
               ,[TENDERED]
               ,[TENDER DATE]
               ,[SALE DATE]
               ,[SELLER NUMBER]
               ,[WEIGHTS]
               ,[DATE PURCHASE PAID]
               ,[PURCHASE DUE DATE]
               ,[PRICE TO BE FIXED?]
               ,[DATE FIXED]
               ,[FUTURES BASIS]
               ,[DIFFRTL PRICE]
               ,[Differentl number]
               ,[PriceDescriptor]
               ,[BBH START UP]
               ,[ADJUSTED NET INSTOCK]
               ,[FORWARD PURCH]
               ,[PAID]
               ,[D/O ALLOCATION]
               ,[WAREHSE RCPT #]
               ,[B/L DATE]
               ,[NOLABELS]
               ,[EXWHS/FOB/ETC]
               ,[H-INDEX]
               ,[PROFORMA PAYMENT]
               ,[PROFORMA DATE]
               ,NULL/*[TITLE DATE]*/
               ,[SAS]
               ,[RCD SHIP ADVICE]
               ,[SHIP COMMENTS]
               ,[MIXED CNTR?]
               ,[No SLACKS]
               ,[READY 2 SHIP MIX CNTR]
               ,[ROYAL SF REF NO]
               ,[REJECTED?]
               ,@datenumber /*[DATE/DISPLAY ORDER]*/
               ,[POLABELS]
               ,[PTBF BY ROYAL]
               ,[PTBF BY SHIPPER]
               ,[APPROVED?]
               ,[APPROVAL DATE INFO]
               ,[MISC(Y/N)]
               ,[TOLL DECAF?]
               ,[NOLABELS1]
               ,@destcity /*[DESTINATION]*/
               ,[MISC HEDGE LABEL]
               ,[FAIR TRADE]
               ,[SHADE GROWN]
               ,[RAINFOREST ALLIANCE]
               ,[SMBC]
               ,[OCIA PRODUCER #]
               ,[OCIA ORIGINAL TRANS CERT#]
               ,[OCIA CERTIFIED?]
               ,[SLACK INDICATOR]
               ,[VESSEL]
               ,[T/S VESSEL]
               ,[VOYAGE NUMBER]
               ,[T/S VOYAGE NUMBER]
               ,[BILL OF LAIDING NUMBER]
               ,[BILL OF LAIDING DATE]
               ,[PORT OF LOADING ID]
               ,[CONTAINER NUMBER]
               ,[VESSEL ETA]
               ,[ARRIVAL PIER ID]
               ,[STEAMSHIP LINE ID]
               ,[CUSTOMS ENTRY NUMBER]
               ,[INBOUND WEIGHING]
               ,[BAG TARE]
               ,[FDA RELEASE DATE]
               ,[CUSTOMS RELEASE DATE]
               ,[Phytosanitary Cert?]
               ,[Shipper Weight cert?]
               ,[Cert of Origin?]
               ,[Fumigation Cert?]
               ,[Packing List?]
               ,[Draying Trucker ID]
               ,@etdmounth /*[MID]*/
               ,@etdyear /*[YEAR ID]*/
               ,@whsenumber /*[FINAL DESTN WHSE]*/
               ,[HOTLIST]
               ,[FLOidno]
               ,[TEST MARKS]
               ,[FUTURESCOMPOSITEinvtry]
               ,[HEDGEadjustment]
               ,[FINAL WHSE-IN COSTadj]
               ,[RFAbuyTransaction]
               ,[WEBinvisible]
               ,@reff_number/*[InventoryTransOrigRef]*/
               ,[NonSKUpricingFlag]
               ,[GrainProDate]
               ,[FOBExhseDescriptor]
               ,[FuturesNameDescriptor]
               ,[FixedPriceOptionCoop]
               ,[SKUaverageprice]
               ,[FuturesHedgeID]
               ,[WhseLocator]
               ,[PaymentTermInvID]
               ,@costtomove/*[FOBtoExWhseValueTemp]*/
               ,[Last_UpdateBy]
               ,1/*[PurchasedDIF_FIX]*/
               ,[shipterm]
               ,[OrgFixationBase]
               ,[orgdatefixed]
               ,[Orgdateinstore]
               ,[GammaProc]
               ,0/*[SupplierCom]*/
               ,0/*[RainforestCom]*/
               ,0/*[OrganicCom]*/
               ,[GTCPrice]
               ,[GTCFilled]
               ,[GTCDateFilled]
               ,[GTCNotes]
               ,[GTCContract]
               ,0/*[IntermediaryCom]*/
               ,0/*[FairTradeMinYes]*/
               ,[FixationCurrentLevel]
               ,[ComOtherName]
               ,0/*[ComOtherLevel]*/
               ,[MetricTonPrice]
               ,[MetricTonDif]
               ,[MetricTonFix]
               ,@mocksaleid /*[TransMockSaleId]*/
        FROM [SQLCAFE2010].[dbo].[Inventory] where [reff number]= @reff_number


    • Edited by John Mendes Friday, November 01, 2013 1:04 PM
    Friday, November 01, 2013 1:03 PM

Answers

All replies

  •  When I try to insert "NULL" into the date field it turns into 1/1/1990   I want to set the date field back to nothing

    This is likely because you are inserting and empty string or zero instead of NULL.  In a parameterized query executed from .NET code, specify DBNull.Value as the parameter value.  The value will then be returned as NULL.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, November 01, 2013 1:10 PM
  • Due to the vast number of attributes I guess it's either dimension or fact table in a data warehouse. In this case check your table DDL as columns in this scenario should not allow NULL values. Also check whether a trigger is involved. On the other hand: where do you get 1900-01-01 (report, SSMS, etc.)?
    Friday, November 01, 2013 1:12 PM
  • Hi,

    Declare @tabtest table (id1 int,datet datetime)
    insert @tabtest values (1,'01/01/2012')
    select * from @tabtest
    update @tabtest set datet = null
    select * from @tabtest
    update @tabtest set datet = ''
    select * from @tabtest

    Try to run this code it can welp you , you have to affect NULL to your field not an empty string.

    Best regards.


    • Edited by KH MR Friday, November 01, 2013 1:14 PM
    Friday, November 01, 2013 1:12 PM
  • He's using NULL. See the line with the /*[TITLE DATE]*/ comment.
    Friday, November 01, 2013 1:37 PM
  • is there a difference between null and NULL  ?
    Friday, November 01, 2013 2:07 PM
  • when looking at the table after running the stored procedure all the other fields copy properly or modified correctly just the date on I want to show null in the table but it shows 1900-01-01


    • Edited by John Mendes Friday, November 01, 2013 2:11 PM
    Friday, November 01, 2013 2:10 PM
  • Hi,

    There is no difference between NULL and null.

    If the field is updated to null it must shows null but if that don't work try to use isnull function or case statement when selecting your data to format the output like what you want.

    Hope that can help,

    Best Regards.

    Saturday, November 02, 2013 11:43 AM
  • is there a difference between null and NULL  ?

    No.

    Most likely, there is a trigger on the table that replaces the NULL with the magic value of 1 Jan 1990.

    You might want to check with "sp_helptrigger inventory".


    Gert-Jan

    Saturday, November 02, 2013 2:38 PM