July 5, 2011 at 6:38 am
Dear Friends,
I know it seems Strange but please help me on this.
My manager wants to change all datetime fields to nvarchar because it is easier to code string against datetime... I told him it is not good because of size of data and validation, but he said those are not important in our projects now.
So I want more reason to make him believe that nvarchar is not good for datetime:(
Help me...
Best Regards,
Ashkan
July 5, 2011 at 6:52 am
It's larger. A datetime is 8 bytes, an nvarchar with a date in the most compact form (20110705) takes 18 bytes.
If can't be used with the datetime functions without conversion.
It has no validation (N'20111332') and hence you need to do your own validation or deal with bad data
The formats can be inconsistent. Prey tell, what date is this? '04082011'. What about '090807'?
There is no way that coding against a string can be easier than against a datetime. Please write me the string manipulation to correctly add one month to the following dates stored as strings: '20000131', '20010131', '20110331'
Compare that against DATEADD(mm,1,'20000131')
I suspect he just isn't aware of the massive number of datetime functions available, functionality that he would have to either re-implement or convert to datetime to use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2011 at 7:05 am
You could say that it is universally known as a bad practice since 1970 and it is very sad and frustrating that somebody still think to know better than Mr.Codd, 40 years after his enlightening paper.
Too harsh?
-- Gianluca Sartori
July 5, 2011 at 7:08 am
why would they use nvarchar?
if you are putting dates into a character column (not that you should) for whatever reason then why use nvarchar surely no dates are going to need unicode characters
July 6, 2011 at 12:38 am
Dear mates,
First of all , thanks for reply,
I will tell some thing more to you just right now;)
Best Regards,
Ashkan
July 6, 2011 at 5:04 am
@Gianluca Sartori
Do you have Mr.Codd enlightening paper.
If yes please send it to me or give me a link.
Best Regards,
Ashkan
July 6, 2011 at 5:49 am
ashkan siroos (7/6/2011)
@steveb. :Thanks dude for reply ,but actually it was not about comparing nvarchar and varchar,
What I mean was to compare datetime VS string fields.
yeah i got that, i was just pointing out how your manager had taken a bad idea (using characters to store dates) and made it even worse (using unicode characters to store dates)
July 6, 2011 at 7:08 am
ashkan siroos (7/6/2011)
@Gianluca SartoriDo you have Mr.Codd enlightening paper.
If yes please send it to me or give me a link.
No, I'm sorry. I don't have that paper. The original work is very difficult to find. I think you can buy it somewhere, but I don't know exactly where.
You can read the story and some excerpts in many Relational Theory books.
However, the main point is that using nvarchar for dates violates 1NF, because it would allow for an attribute values outside of the active domain. Also, values cannot be handled directly by the RDBMS and cannot be referenced in constraints.
This should be enough to give up.
-- Gianluca Sartori
July 6, 2011 at 9:25 pm
Aside from the 1NF violation, it introduces some additional potential issues.
For example, all of these are valid date formats:
mm/dd/yy
dd/mm/yy
yy/mm/dd
Now... if I have the following in my "character date field":
01/02/03
What does it mean? Because it could be:
1 February, 2003
or
2 January 2003
or
3 February 2001
The way to get around this is to force one and only one format to be used. To do that... you have to build a constraint against the database to ensure it (and as with the example above, even then you can't be sure.) Also, you now have to write logic to convert the various date formats.
So you're creating more work for yourself, and introducing potential for all sorts of interesting bugs.
July 6, 2011 at 9:31 pm
ashkan siroos (7/5/2011)
My manager wants to change all datetime fields to nvarchar because it is easier to code string against datetime... I told him it is not good because of size of data and validation, but he said those are not important in our projects now.
This sounds like an app coder deciding database rules. He doesn't want to have to cast every time he pulls a value from or pushes it to the database. Lord knows why he'd want a string in code instead of a julian date, but there's a few programming languages out there that work with ISO dates (YYYYMMDD) just fine as strings.
If we're to help you fight the good fight, you need to go back and speak with him and find out exactly what benefit he expects he's going to get from using string variables instead of date variables. From there we can give you particular arguments to help you convince him of the poor choice he's making. We're throwing around reasons here, but if they don't fit the specific debate, they're going to come across as secondary considerations.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 6, 2011 at 9:35 pm
Good advice, Craig. I hadn't thought about it from that point of view.
July 6, 2011 at 9:55 pm
Bruce W Cassidy (7/6/2011)
Good advice, Craig. I hadn't thought about it from that point of view.
You get used to hearing the arguments when the database is being used as a data-store instead of a data-engine. This one has all the earmarks. 🙂
No worries, Bruce, you just have to have had to bang your head on that particular wall often enough. 😎
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 16, 2011 at 11:27 am
ashkan siroos (7/5/2011)
My manager wants to change all datetime fields to nvarchar because it is easier to code string against datetime...
Why is it "easier" to code that way? Strikes me it's easier to have an app. create a date if that's what's needed.
Perhaps you could appeal to his vanity and future company expansion? "Gee boss, coding standards are harder to enforce as we grow, and not everyone who develops code will necessarily be as diligent as you to ensure we only store valid dates." 😉 He did say it's not important "now", but you want to think ahead.
Of course it's all stupid, but as Craig pointed out above, you need to deal with stupid as it stands. I worked on a project that had no transactional consistency b/c UPDATEs were done one-at-a-time, table columns contained multiply embedded pieces of data that not only belonged in their own columns, they were actually deserving their own tables, etc. etc.
It did me no good to explain this was a gross violation of standard design and coding practice. I had to know the business well enough to be able to ask things like "Well, if we stuff all this information into 1 table column, what happens when the A/R department needs that embedded data and cannot query on it to generate past-due account reports? Will that make collections harder?"
Post back with your outcome, good or bad. We'd like to hear what happens,
Rich
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply