Can I convert date format for this value '19920000'?

Question

I have a value 19920000. This is date format yyyyMMdd. I want to save by date field in the table. You may see it is invalid data because day and month value is zero. Can I convert date format to this value in SQL Server? Can or not?

How to save just only year in date field?

I get the data from excel sheet. These data are customer birth date. Some people want to hide day and month of birth date. They don’t gave to right date format. My application is read data from excel sheet and show on datagridview table and then save to database. The data is big amount which round about 30000 records above at least for one time read. I can’t separate column for this data field because performance may be down.

I am using SQL Server 2008.

.

Answer 1

The date type can't store partial dates, like only year, or only day and month.

In one project I had a need to store incomplete dates and I used three separate nullable columns for day (tinyint), month (tinyint) and year (smallint). I don't see in what cases you'd need to use varchar instead of integer types.

In my case it was information about customer birth dates and quite often salespeople didn't know the year, but knew month or day. With this approach it is very flexible and allows the user to enter any bit of information he has. It was also important to know that certain part of the date is missing, such as "we don't know the exact birth date, but we know that it is in June".

Obviously, there were a bunch of custom checks in the application (not at the database level) trying to prevent entering things like 31st of June. In that particular project it was OK to perform the checks in the application. Most likely it would have been possible to implement them in T-SQL as well. In general, it is better to have constraints implemented at the database level, because applications come and go, but data remains.

It was more difficult to use these incomplete dates in queries as well. Different queries/reports required different approaches to dealing with the missing bits.

30K rows is not much at all and performance should not be a problem. So, I'd focus on smart parsing and validation of your Excel data and storing all information that you can extract.

Answer 2

I think you can't add this value as date type. Because date type is special type, you want to use it so you must accept its rules which are defined by Microsoft. You may use another type like varchar to save your value easier.

It surely can not use wrong syntax of date type. If you want to save year only (just for querying, no need to compare or calculate, you can save it as varchar type. If not, you may save it as int and add a constraint such as trigger or check for limiting the input data.