Cast SQL Column definition and default value

Question

I have the following table create definition in SQL Server:

CREATE TABLE [dbo].[EventLog] 
(
   [RawEvent] NVARCHAR(MAX) NOT NULL,
   [ReceivedDate] AS CAST(JSON_VALUE(RawEvent, '$.ReceivedTime') AS DATETIME)
)

When I receive a JSON string, that string has ReceivedTime which I parse out and use that as the ReceivedDate column's value when inserting this record.

This is currently working. However, when RawEvent string doesn't have the property ReceivedTime, I want to use the current SQL date time as the default value instead of NULL which is what it is doing now.

Is this possible to do in the table definition?

You can use an on-insert trigger on the table to overwrite a NULL value with GETDATE. — Russell Fox
– Russell Fox, Commented Aug 2, 2019 at 17:28
Or make a second column (maybe ReceivedDate2), default it with GETDATE(), and then have a computed column do a CASE WHEN ReceivedDate IS NULL THEN ReceivedDate2 ELSE ReceivedDate END or something similar. This may prove additionally useful as you will have a timestamp for the row, always. — Jacob H
– Jacob H, Commented Aug 2, 2019 at 17:38
Does AS COALESCE(CAST(JSON_VALUE(RawEvent, '$.ReceivedTime') AS DATETIME, GETDATE())) work? (It should but I don't have any valid JSON to try with.) — Aaron Bertrand
– Aaron Bertrand, Commented Aug 2, 2019 at 17:48
@RussellFox I don't think a trigger can directly update a computed column. — Aaron Bertrand
– Aaron Bertrand, Commented Aug 2, 2019 at 17:49
@AaronBertrand I'll test this out, if it works your solution is a great and elegant way. — Fylix
– Fylix, Commented Aug 2, 2019 at 17:53

Hogan · Accepted Answer · 2019-08-02 18:01:20Z

2

In SQL you can use COALESCE to supply a default value instead of null

COALESCE(CAST(JSON_VALUE(RawEvent, '$.ReceivedTime') AS DATETIME),GETDATE())

answered Aug 2, 2019 at 18:01

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hogan Over a year ago

ooops, I just saw that aaron had this in the comments -- I also didn't test

Jacob H · Accepted Answer · 2019-08-02 17:56:18Z

While I think @AaronBertrand idea of COALESCE is more straightforward, here's a working example using computed columns:

CREATE TABLE [dbo].[EventLog] (
   [RawEvent]               NVARCHAR(MAX)       NOT NULL,
   [ReceivedDate]           AS CAST(JSON_VALUE(RawEvent, '$.ReceivedTime') AS DATETIME),
   [ReceivedDateDefault]    DATETIME NOT NULL DEFAULT GETDATE(),
   [ReceivedDateCalculated] AS (CASE WHEN JSON_VALUE(RawEvent, '$.ReceivedTime') IS NULL 
                                        THEN [ReceivedDateDefault] 
                                        ELSE JSON_VALUE(RawEvent, '$.ReceivedTime') END),
)

INSERT INTO dbo.EventLog (RawEvent)
VALUES
(   N'{"Foo":"Bar",
        "ReceivedTime":"2019-08-02"
        }'),
(   N'{"Foo":"Baz"
        }')

And the results:

SELECT ReceivedDateCalculated FROM dbo.EventLog

ReceivedDateCalculated
2019-08-02 00:00:00.000
2019-08-02 13:51:14.910

Might be some funky edge cases with empty strings or something, you would definitely want to consider that. There is one additional benefit of having the second date column, that you will always have a timestamp on this row (if you don't already). Which I've found tends to be pretty useful.

Collectives™ on Stack Overflow

Cast SQL Column definition and default value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related