I have a field called vaccinationdate in a table with datatype DATE, but the values of that field have different formats. now I cant change the datatype of this column and the client says this is how the values will be since in case if the patient doesn't know the exact date of vaccination they would just mention the month and year. please help me to insert these values in the table.
ex: Values are like:
06/17/2016
07/12/2016
07/2016
05/28/2016
02/2016
If you want to keep the data in the format that the user provided, namely sometimes without dates, sometimes with dates etc., you cannot use a column of type DATE. VARCHAR or NVARCHAR is your only option (e.g. VARCHR(16)). However, using a character column for something that should be a date is not a good idea; someone could put total garbage into the column if you do that.
Presumably you have a stored procedure or code fragment that receives the user input, and inserts it into the database table. Assuming that that is the case, do validation checks in that code and send a valid date to database. For example, if the user inputs 07/2016, you might want to set an arbitrary date (07/01/2016) and store into the database.
If you have control over the user input portion of it, pretty much any user interface building tools allow you to display a date picker to the user, so they can pick a date, (or type in a valid date). That would make the validation check requirements much less stringent and more robust.
How is the vet going to send out a reminder based on a vague date?
I think better to store a real data (artificially choose by the operator, if necessary) and then have an "Exact/Approx" checkbox or somesuch to indicate if the date is a guess. Then you can send reminders after one year, say, and count "How many follow-ups have I had / NOT had" and so on.
You should store each part of the date separately. That way, if the day value is unknown, you can leave it null. You could store the original string as "documentation" of what was originally entered, but do not try to use it as actual data. Unstructured date data is a nightmare to try to process.
vaccination_year smallint NULL --only NULL if never vaccinated(?)
vaccination_month tinyint NULL --NULL if unknown or never vacc
vaccination_day tinyint --NULL if unknown or never vacc