SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Syntax for default value for NULLS in colun=mn
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Analyzer
Posting Yak Master

United Kingdom
101 Posts

Posted - 02/26/2014 :  10:53:54  Show Profile  Reply with Quote
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

Canada
71 Posts

Posted - 02/26/2014 :  11:01:05  Show Profile  Reply with Quote
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

United Kingdom
101 Posts

Posted - 02/26/2014 :  11:54:43  Show Profile  Reply with Quote
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

United Kingdom
101 Posts

Posted - 02/26/2014 :  11:59:48  Show Profile  Reply with Quote
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

Canada
71 Posts

Posted - 02/26/2014 :  12:12:31  Show Profile  Reply with Quote
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

Canada
71 Posts

Posted - 02/26/2014 :  12:25:14  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000