I have this column that contains 'NO RECORD' values that I used to replace the NULL values and I want to change the column type into a date but I get this message :
Conversion failed when converting date and/or time from character string.
Of course, the reason behind it is the No Record values, so how I can keep the strings while changing the column into date, is that possible, because I want to fill my Null Values instead of keeping them empty
>Solution :
Sounds like your requirement is:
- Store a date (or datetime) value
- But, for a given row, you will not always have a known value
- When the data(time) is not available, show users "No Record"
In a relational database, this is best implemented with a column defined with an appropriate date/time datatype (date, datetime, datetimeoffset, whatever is most appropriate), with the column set as NULLable.
- When the value is known, store the value
- When the value is not known, store it as NULL
That accounts for data storage. How to display data to users is a different subject. No, really. Date/time data is not stored as a string of numbers and punctuation, it is stored in a very specific binary format. When the data is retrieved, it is formatted as the UI requires, even if that’s only SSMS. After all, is September 10, 2011 displayed as
- 09/10/11 (US)
- 10/09/11 (UK)
- 11/09/10 (Sortable)
My point here is, if when the date(time) is not known you need to show the consumers of your data the string of characters "No Record", you will need to put in special handling on your UI to check to do this. An example:
SELECT isnull(date_onset, 'No Record') date_onset
from dbo.MyTable