Inserting a default value for a datetimeoffset column from a json data field that has a null value

Transact-SQL
,
1

I am using a stored procedure that inserts a json document. Everything was 'working' until an issue arose where one of the datetime fields in the json document had a null value.

What's odd is the sql column it was inserting into has allow nulls set to no but I had a default date set to GETDATE(), so first I tried using GETUTCDATE(), and SYSDATETIMEOFFSET() and both are not setting the default date.

So next I tried changing the insert statement(trimmed up for brevity)

INSERT INTO 
			ChannelAdvisorOrderFulFillment(
			SiteName, SiteOrderId, CreatedDateUtc, PaymentDate, EstimatedShipDate, ShipmentDateUtc, 
			ShipmentTrackingUrl, ShipmentCarrier, ShipmentService, ShipmentCost, DeliverByDateUtc)
			SELECT o.[SiteName], o.[SiteOrderID], CAST(NULLIF(o.[CreatedDateUtc], '') AS datetimeoffset),	CAST(NULLIF(o.[PaymentDateUtc], '') AS datetimeoffset),
				   CAST(NULLIF(o.EstimatedShipDateUtc, DATEADD(HOUR, 12, o.[PaymentDateUtc])) AS datetimeoffset), CAST(NULLIF(s.ShippedDateUtc, '') AS datetimeoffset), s.TrackingNumber,	
				   s.TrackingUrl, s.ShippingCarrier, s.ShippingClass, s.ShippingCost, CAST(NULLIF(o.DeliverByDateUtc, '') AS datetimeoffset)
			FROM OPENJSON(@payload)			
			WITH(
				OrderID bigint '$.OrderID',
				ID bigint '$.ID',
				[Type] nvarchar(250),
				ShippedDateUtc nvarchar(50),
				TrackingNumber nvarchar(100),
				TrackingUrl nvarchar(MAX),
				ShippingCarrier nvarchar(150),
				ShippingClass nvarchar(150),
				ShippingCost nvarchar(50),
				[Order] NVARCHAR(MAX) AS JSON
			) s
			CROSS APPLY OPENJSON(s.[Order])
			WITH(
				DeliverByDateUtc nvarchar(50),
				ShippingCity nvarchar(250),
				ShippingStateOrProvince nvarchar(100),
				ShippingPostalCode nvarchar(50),
				ShippingCountry nvarchar(50),
				SiteName nvarchar(250),
				SiteOrderID nvarchar(250),
				CreatedDateUtc nvarchar(50),
				PaymentDateUtc nvarchar(50),				
				EstimatedShipDateUtc nvarchar(50)
				
			) o

heres the json payload

DECLARE @payload nvarchar(MAX) = '{
   "schema":"/v1/fulfillments/sellerfulfillmentstatuschanged",
   "eventDateUtc":"2021-12-06T04:37:37.63586Z",
   "webhookId":1,
   "payload":{
		"ID":17749251,
		"ProfileID":12345678,
		"OrderID":23355465,
		"CreatedDateUtc":"2021-12-06T04:21:34.7766667Z",
		"UpdatedDateUtc":"2021-12-06T04:37:37.4866667Z",
		"Type":"Ship",
		"DeliveryStatus":"Complete",
		"TrackingNumber":"ABCD123",
		"ReturnTrackingNumber":null,
		"ShippingCarrier":"Amazon Merchants@",
		"ShippingClass":"NextDay",
		"DistributionCenterID":3,
		"ExternalFulfillmentCenterCode":null,
		"ExternalFulfillmentStatus":"New",
		"ShippingCost":0.0000,
		"InsuranceCost":0.0000,
		"TaxCost":0.0000,
		"ShippedDateUtc":"2022-01-06T04:21:34.7766667Z",
		"SellerFulfillmentID":null,
		"HasShippingLabel":false,
		"HasChannelPackingSlip":false,
		"HasReturnLabel":false,
		"HasChannelReturnLabel":false,
		"ExternalFulfillmentNumber":null,
		"ExternalFulfillmentReferenceNumber":null,
		"ShippingLabelRequestID":null,
		"StagingLocation":null,
		"LabelFormat":null,
		"ReturnLabelFormat":null,
		"ChannelReturnLabelFormat":null,
		"Order": {
			  "ID":23355465,
			  "ProfileID":12345678,
			  "SiteID":640,
			  "SiteName":"Amazon Seller Central - US",
			  "UserDataPresent":1,
			  "UserDataRemovalDateUTC":null,
			  "SiteAccountID":6333961,
			  "SiteOrderID":"353-5497167-9312614",
			  "SecondarySiteOrderID":null,
			  "SellerOrderID":null,
			  "CheckoutSourceID":1,
			  "Currency":"USD",
			  "CreatedDateUtc":"2021-12-06T04:21:34.7766667Z",
			  "ImportDateUtc":"2021-12-06T04:37:37.41Z",			 
			  "EstimatedShipDateUtc":null,
			  "DeliverByDateUtc":null,
			  "RequestedShippingCarrier":"Amazon Merchants@",
			  "RequestedShippingClass":"NextDay",
			  "ResellerID":null,
			  "FlagID":0,
			  "FlagDescription":null,
			  "OrderTags":"TestOrder,AutoGeneratedSku",
			  "DistributionCenterTypeRollup":"SellerManaged",
			  "CheckoutStatus":"Completed",
			  "PaymentStatus":"Cleared",
			  "ShippingStatus":"PartiallyShipped",
			  "CheckoutDateUtc":"2021-12-06T04:21:34.7766667Z",
			  "PaymentDateUtc":"2021-12-06T04:21:34.7766667Z",
			  "ShippingDateUtc":null,
			  "BuyerUserId":"wgqvr897lr7dawk55887@marketplace.amazon.com",
			  "BuyerEmailAddress":"wgqvr897lr7dawk55887@marketplace.amazon.com",
			  "BuyerEmailOptIn":false,
			  "OrderTaxType":"Standard",
			  "ShippingTaxType":"Standard",
			  "GiftOptionsTaxType":"Standard",
			  "PaymentMethod":"Amazon",
			  "PaymentTransactionID":null,
			  "PaymentPaypalAccountID":null,
			  "PaymentCreditCardLast4":"",
			  "PaymentMerchantReferenceNumber":null,
			
		},
		"Items":[
		   {
			  "ID":19382370,
			"Sku": "Test01",
			  "ProfileID":12345678,
			  "FulfillmentID":17749251,
			  "OrderID":23355465,
			  "OrderItemID":23870765,
			  "Quantity":1,
			  "ProductID":15072384,
			  "SellerFulfillmentItemID":null,
			  "MarketplaceShippingStatus":"Pending",
			  "DistributionCenterItemUnitCost":null,
			  "DistributionCenterShippingCost":null,
			  "DistributionCenterCalculatedItemUnitCost":null,
			  "DistributionCenterCalculatedShippingCost":null
			"ReferenceSku": null,
			"ReferenceProductID": null,
		        "WarehouseLocation": null
		   }
		]
   }
}'

Any help is appreciated

2

Thanks for the rubber ducking session!

I forgot about COALESCE

COALESCE(o.[CreatedDateUtc], GETUTCDATE()) AS [CreatedDate],	
COALESCE(o.[PaymentDateUtc], o.[CreatedDateUtc]) AS [PaymentDate],					
COALESCE(o.EstimatedShipDateUtc, DATEADD(HOUR, 11.5, GETUTCDATE())) AS EstimatedShipDate,					
COALESCE(s.[ShippedDateUtc], GETUTCDATE()) AS [ShippedDate], 
COALESCE(o.DeliverByDateUtc, DATEADD(DAY, 10, GETUTCDATE())) AS DeliverByDate

This is what I used and its working.

3

I think you where confused with the ISNULL and the NULLIF function.

4

Nailed it