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.
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)_! |
|
|
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 |
|
|
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_dateFROM [tbl] |
|
|
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)_! |
|
|
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_dateFROM [tbl]!_(M)_! |
|
|
|
|
|
|
|