T-SQL Help for date field

1

Hi, when I convert an empty field to date gives 1900-01-01, converting null to date gives null.
I have a SQL View where I transform rows to columns.

Here are my 2 lines:

(SELECT Convert(date,[Value]) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Created') AS "Created",
(SELECT Convert(date,[Value]) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Closed') AS "Closed",

I need to use something like this:

SELECT CONVERT(DATE, NULLIF([Value], ''))

NULLIF replaces a value with null if it matches a string, in this case an empty string

Help? I am lost on how to implement.

Thanks!

2

You can use ISNULL to replace a NULL value to something.

SELECT CONVERT(DATE, ISNULL(NULLIF([Value], ''), GETDATE())

In this case a NULL value will be replaced with the current date. You can also use ISDATE().

SELECT
     CASE 
           WHEN ISDATE(ISNULL([Value],""))=1 THEN 
                CONVERT(DATE, [Value]) 
           ELSE GETDATE() 
    END AS [YourDate]
1 Like
3

Hi,
thanks for your reply.
how do i put this into action?
what is the syntax?
here is a snippet again of my 'Closed' field that needs this action to be performed on:

(SELECT Convert(date,[Value]) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Closed') AS "Closed",

thanks!

4

Hi again,
Ignore what I said earlier, I think I've done it now. I didn't need GETDATE, I needed a NULL:

(SELECT CONVERT(DATE, ISNULL(NULLIF([Value], ''), null)) FROM ActivityEntryData WHERE ActivityEntryId = A.LastActivityEntryId AND [Key] = 'Closed') AS "Closed",

Thanks!