CODEWITHSUNDEEP
sqlsql-serverisnull
Pranay Kumar
Pranay Kumar

Reputation: 78

isnull function does not return correct

enter image description here

When I am using isnull it does not return the '' please see below I have original DOB, isnull used, cast as date.

Upvotes: 1

Views: 391

Answers (2)

Renden Barretto
Renden Barretto

Reputation: 11

if you really would like to return an empty string for the date value, you could try this in a new query window. It creates a table to repoduce your requirement of a null date value and then selects the value before dropping the table.

CREATE TABLE dbo.Test
(
Id INT IDENTITY(1,1) NOT NULL
,Date1 DATE NULL
)

INSERT INTO dbo.Test(Date1) VALUES ('01/01/2017')
INSERT INTO dbo.Test(Date1) VALUES ('01/02/2017')
INSERT INTO dbo.Test(Date1) VALUES (NULL)
INSERT INTO dbo.Test(Date1) VALUES ('01/04/2017')

SELECT * FROM dbo.Test

SELECT Date1 = CASE WHEN date1 IS NULL THEN '' ELSE CAST(DATE1 AS    VARCHAR(10)) END from dbo.Test

DROP TABLE dbo.Test
go

Upvotes: 1

SqlZim
SqlZim

Reputation: 38063

You would need to convert dob to a char/nchar/varchar/nvarchar type to use isnull() or coalesce() like that.

select isnull(convert(varchar(10),dob,120),'')

Upvotes: 1

Related Questions