Printable View
What is it with Date questions lately? Seems to be a lot of them, mostly dealing with them as strings or variables.
Anyway, I treat them as strings, because all I have to do is format them into mm/dd/yy format. Don't do any calculations with them. All we do is display them.
If you do have to work with dates, you will have to use a Date datatype, or convert them somehow.
Dates are just confusing...
First they are strings - so they need delimiters. And those vary from #'s to quotes to whatever mySql might use and all of that is based on the provider as well.
So people transistion from one DB to another and don't know the changes in those rules.
Then we have the global insanity of MM/DD or DD/MM and what the DB prefers!
New programmers don't want to think about storage format and input format and display formats (and sort formats for that) all those being different. Most datatypes are simple - dates are basically the worst.
btw - we also treat all our dates as strings - which is frowned upon by most on the forum here. We have our users input dates as MMDDYYYY without the slashes - have our own validation routines for what they enter and then we put the slashes in place for display purposes. When we grab a date in SQL we always use Convert(varchar(10),datefield,101) so it formats as 01/01/2008. Always pass the date from server to VB as a string.
Whenever I'm in Mgt Studio and free-hand typing a query I always specify a date as YYYY-MM-DD - knowing that format is fully accepted and understood. It comes naturally to type it that way...
...then someone discovers that a time component is in the date field as well and it just gets worse ;)
They are a bit more complex than other data types, but not overwhelmingly so.. I can't think of a better way to implement them myself.
I've yet to find a DBMS/driver which deals with anything other than US (DD/MM/YYYY) or ISO (YYYY-MM-DD) formats, as that is what the SQL standards dictate.Quote:
Then we have the global insanity of MM/DD or DD/MM and what the DB prefers!
The delimiters etc can be awkward, as they vary for virtually every DBMS.. hence the per-DBMS date examples in the article How do I use values (numbers, strings, dates) in SQL statements?
Of course if you use parameterised queries, you can simply ignore these issues entirely (as you simply put a VB date into the parameter, and it is formatted & delimited for you).
The only odd issue is the time component, which can of course mess up your queries unless you are aware of it.