Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Syntax for default value for NULLS in colun=mn

Author  Topic 

Analyzer
Posting Yak Master

115 Posts

Posted - 2014-02-26 : 10:53:54
From standard SELECT need to display specific column values normally, together with any NULLs in the same column not as NULL.

My below effort parses however does not change to <new_value>. Please advise a solution to achieve this.


SELECT [Server],
[db_name],
(CASE WHEN last_db_bkup_date IS NULL THEN '<new_value>' ELSE last_db_bkup_date END) AS last_bkup_date, [Backup Age (Hours)]
FROM [tbl]

Thanks

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-02-26 : 11:01:05
Is this <new_value> constant or any fix value? Can you give some example?
If it is only one value for <new_value> then you can you ISNULL(last_db_bkup_date,<new_value>) instead of CASE statement.

!_(M)_!
Go to Top of Page

Analyzer
Posting Yak Master

115 Posts

Posted - 2014-02-26 : 11:54:43
Problem is when a NULL is present in the datetime column it shows as 2000-01-01 00:00:00.000 when looked at in a SSRS report. Meaning the column has genuine dates and many 2000-01-01 00:00:00.000 values making the report look bad. Wanted to change the 2000-01-01 00:00:00.000 to a varchar alternative such as 'no_record' which improves logic to the nature of the report.

Will try to option you provided and let you know the result.

Thanks
Go to Top of Page

Analyzer
Posting Yak Master

115 Posts

Posted - 2014-02-26 : 11:59:48
I tried this but unable to parse. Anything I could change?

SELECT [Server],
[dB_name],
ISNULL(last_db_bkup_date,'no_record'), ELSE last_db_backup_date END) AS last_backup_date
FROM [tbl]
Go to Top of Page

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-02-26 : 12:12:31
Try using conditional formatting in SSRS expression.
e.g. IIF(YEAR(Fields!last_db_bkup_date.Value)= 2000,"No Record",Fields!last_db_bkup_date.Value)

!_(M)_!
Go to Top of Page

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-02-26 : 12:25:14
Try:

SELECT [Server],
[dB_name],
ISNULL(CONVERT(nVarChar(30), last_db_backup_date, 121), 'No Record') AS last_backup_date
FROM [tbl]

!_(M)_!
Go to Top of Page
   

- Advertisement -