SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date showing instead of blank result


Date showing instead of blank result

Author
Message
cmw 66135
cmw 66135
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 128
Hi

I am getting a date show in one of my fields and I know this box is blank on the database. Is there a way of me showing this as a blank result instead of what I am being returned on the report builder?

Thanks
Chris
Thom A
Thom A
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69955 Visits: 18430
If the value is NULL, then SSRS will display a NULL.

What is the expression in the textbox?


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
cmw 66135
cmw 66135
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 128
At the moment just the field it is pulling from, I have tried a few expressions that haven't worked I am getting 1/1/1753 12:00:00AM returned but I know the box is not completed on the system
Thom A
Thom A
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69955 Visits: 18430
cmw 66135 - Wednesday, December 13, 2017 4:53 AM
At the moment just the field it is pulling from, I have tried a few expressions that haven't worked I am getting 1/1/1753 12:00:00AM returned but I know the box is not completed on the system

What is the datatype of the column in the database? Is it really a date, or is it a varchar, with a date in it? As I said before, if the column has a column of NULL, nothing will displayed, so the value of the column being returned in your dataset appears to not be NULL (remember an empty string ('') is not the same as NULL).



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
cmw 66135
cmw 66135
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 128
How do I find the datatype. I am only assuming it is a date as that is what the field show be on our system?

I assume there is a way to show that return field as a blank if that is its default return value?
Thom A
Thom A
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69955 Visits: 18430
cmw 66135 - Wednesday, December 13, 2017 5:05 AM
How do I find the datatype. I am only assuming it is a date as that is what the field show be on our system?

I assume there is a way to show that return field as a blank if that is its default return value?


You'll need to check the query returning the dataset. If they isn't doing any conversion, check the table the column is being returned from.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
cmw 66135
cmw 66135
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 128
Not sure how to do what you suggested, But I did download the returned values to excel and the entries that are showing that date are being downloaded as -53689. So is there a way of me showing that as blank?
sql Sarah
sql Sarah
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1483 Visits: 609
cmw 66135 - Wednesday, December 13, 2017 6:03 AM
Not sure how to do what you suggested, But I did download the returned values to excel and the entries that are showing that date are being downloaded as -53689. So is there a way of me showing that as blank?


Can you remove the expression for the field from the report output, but leave the field name?
Example: Employee ID Employee Name Hire Date
Fields.Emp_ID.Value Fields.Emp_Name.Value
HappyGeek
HappyGeek
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12596 Visits: 6323
cmw 66135 - Wednesday, December 13, 2017 6:03 AM
Not sure how to do what you suggested, But I did download the returned values to excel and the entries that are showing that date are being downloaded as -53689. So is there a way of me showing that as blank?


Just a guess here but the field is a datetime field which is not nullable, therefore when you save a record with no value it will return the default minimum value. The figure you see in Excel should be -53690, which if you run

select cast(-53690 as datetime)

returns a value of 1753-01-01 00:00:000, I would suggest putting an IF statement in the field which checks the value and returns blank if it is this value, otherwise it returns the stored value.
Hope that makes some sort of sense, falling asleep rapidly Smile

...
cmw 66135
cmw 66135
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 128
Hi Happygeek,

Thanks for that. It worked like a charm.

Regards
Chris
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search