Return a blank for undesired value 1

[npp83]

I have a series of date columns that are generating (on occasion) an un desired date (1900-01-01). I want to be able to isolate these dates per column and simply return a blank (' ') rather than a NULL or anything else.

An example (partial) query I am using is currently this:

+ '|' + CAST(CONVERT(varchar(10),ISNULL(dbd1._2149, ''), 110) AS VARCHAR) --AS 'LockDate'

I need to be able to fit whatever I need into this selection statement where I am doing a cast and a null check. And yes, this is supposed to be pipe-delimited. So how can I return a blank where dbd1._2149 = '1900-01-01?

 

[RiverGuy]

SELECT
SomeColumns,
Something + '|' + CASE
  WHEN dbd1._2149 = '19000101' THEN ''
  ELSE CAST(CONVERT(varchar(10),ISNULL(dbd1._2149, ''), 110) AS VARCHAR)
END AS 'LockDate'

 

[gmmastros]

[tt]
+ '|' + CAST(CONVERT(varchar(10),ISNULL([!]NullIf([/!]dbd1._2149[!], '19000101')[/!], ''), 110) AS VARCHAR) --AS 'LockDate'
[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Looking at your code, I see that you are converting to varchar (style 110) and then casting to varchar. The last cast is not needed.

[tt]
+ '|' + CONVERT(varchar(10),ISNULL(NullIf(dbd1._2149, '19000101'), ''), 110) --AS 'LockDate'
[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[npp83]

Well, neither of your responses work off-the-bat. However, I am pretty sure that both are on the right track. In either case I am still pulling the undesired date. That being said, I am pasting a larger clip of my query so you see how it is. Ive incorporated RiverGuy's solution, and as I said before it doesnt work, but that doesn't mean its wrong. I am pretty sure the case is the best way to handle it. Maybe looking below will help show you what I am doing. Keep in mind the --As 'LockDate' is only there as a comment not an actual alias.

SELECT DISTINCT

ls.LoanNumber
+ '|' + --Source/Branch--
+ '|' + CAST(ISNULL(ls.LoanOfficerName, '') AS VARCHAR)
+ '|' + CAST(ISNULL(ls.BorrowerLastName, '') AS VARCHAR)
+ '|' + CAST(ISNULL(dbn1._1109, 0) AS VARCHAR) --AS 'LoanAmount'
+ '|' + CAST(ISNULL(dbs1._1401, '') AS VARCHAR) --AS 'LoanProgram'
+ '|' + CAST(ISNULL(dbn1._3, 0) AS VARCHAR) --AS 'InterestRate'
+ '|' + CAST(ISNULL(dbn1._2161, 0) AS VARCHAR) --AS 'GrossPrice'
+ '|' + CAST(ISNULL(dbn1._2202, 0) AS VARCHAR) --AS 'TotalPriceAdjustments'
+ '|' + CAST(ISNULL(dbn1._2203, 0) AS VARCHAR) --AS 'BOTPrice'
+ '|' + CASE
WHEN dbd1._2149 = '19000101' THEN ''
ELSE CAST(CONVERT(varchar(10),ISNULL(dbd1._2149, ''), 110) AS VARCHAR)
END --AS 'LOCK DATE'

 

[gmmastros]

What is the data type for this column?

Both of our solutions assumed it was DateTime (or SmallDateTime).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[npp83]

As for your recent post, gmmastros. I did knock off that extra cast as per the convert. I am sure that wasn't doing anything either. Thanks. Now what of my recent post regarding River Guy's solution?

 

[npp83]

I confirmed that it is a datetime.

 

[gmmastros]

It's the IsNull you have in the code that is causing your 1900-01-01 problem. To see what I mean, copy paste this to a new query window and run it.

Declare @D DateTime
Set @D = NULL

Select IsNull(@D, '')

This code returns 1900-01-01 00:00:00.000.

I think you have NULLS in the database, which are getting converted to 1900-01-01.

If I am not mistaken, this should solve your problem.

+ '|' + IsNull(CONVERT(varchar(10),NullIf(dbd1._2149, '19000101'), 110), '') --AS 'LockDate'

Basically, you need to convert your date to a string before the isnull check instead of after it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom