Why doesn't SQL Server allow me to separate DATE and TIME?
Admittedly, this is one of the rare features that Access boasts over SQL Server. The ANSI-92 standard states that compliant database should support the following DATE/TIME datatypes:
Here are the results:
Notice that SQL Server inserts midnight when time information is missing, and 1900-01-01 when date information is missing.
So what do you do when you're only interested in one or the other? There are several camps on this one. One is to store the date and/or time information as a CHAR or VARCHAR column. This makes comparisons and sorting very difficult. Another camp suggests storing the extraneous information and ignoring it. Often "ignoring" means "converting", so to get just the date or time from the above table, you would do this:
Unfortunately, this type of conversion will not take advantage of any index on the DATETIME column. A similar approach is to store a standard value for the part you're not interested in, and handle that part of the data at the application level.
Another way to store only time, and do so efficiently, is to use an integer column. You multiply the number of hours by 100, and add the minutes. For example:
You multiply the hours by 100, then add the minutes, thus getting the time in military format. You could leave the formatting up to the application, or retrieve a nicely formatted time by running this query against #foo (I think this would be prettier in an application that's not so strongly typed):
Similarly, to store only date as an integer, you multiply the year by 10000, add the month (multiplied by 100), and then add the day:
Getting this one into date format is about as pretty as the previous example:
These latter solutions should only be used for enhancing storage space required for presentation values. If you need to do computations and conversions on these columns, throw the last few solutions out the window.
Related ArticlesCan I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?
Could I get some help with JOINs?
How can I tell which version of MDAC I'm running?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I change column order in a table structure?
How do I change the order of columns in a table?
How do I concatenate strings from a column into a single row?
How do I convert columns of values into a single list?
How do I determine if a database exists?
How do I document / compare my SQL Server database(s)?
How do I get the IDENTITY / AUTONUMBER value for the row I inserted?
How do I solve 'ADO Could Not Find The Specified Provider'?
Should I use BETWEEN in my database queries?
Why can't I use the * wildcard in a database search?
Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
Why do I get weird results when using both AND and OR in a query?
Why do some SQL strings have an 'N' prefix?
Why does AbsolutePosition return as -1?
Why is Query Analyzer only returning 255 characters?
Why should I avoid NULLs in my database?
How do I deal with an apostrophe (') in a SQL statement?