hi
i need to find data between 2 date's and time's.
i use one field for date , and one field for time.
is it be better to use only one field for date & time ?
i see that it came in dd/mm/yyyy hh:mm:ss format that
can contain date and time.
this question is for acceess and for sql-server
thank's in advance
depends on the requirement. If you are using sql server 2008+ then if you store in separate is not a problem, as well as it is as easy option to write the query
In nearly all circumstances, date and time are needed together. Both Access and SQL server have a date/time data type. In Access, even if you specify the format as time, you can show a date. This is because all datetime data is stored as a number, and time is the decimal portion:
Say I store data: 10:31:46, I can type lines in the immediate window that illustrate the storage of datetime, like so:
?CDec(DlookUp("TimeFormattedField", "Test"))
0.438726851851852
?Year(DlookUp("TimeFormattedField", "Test"))
1899
?Format(dlookup("F4", "Table2"),"dd/mm/yyyy")
30/12/1899
This is because zero (0) is a valid date.
It is very easy to get the different portions of a datetime field, so store datetime as a single fields, because that is what you are going to get, anyway.
I like to store date and time separately. In general, I almost never need time in my apps. One case where I store them separately is in some of my logging routines. This is mostly because I only ever query on dates and never on date+time.
If you need to query on both date and time, then storing them separately is really problematic, because then you have to concatenate two fields for comparison, and that means your criteria won't use any indexes on the two fields. This may not be an issue for a few thousand records, but for anything above that, it can quickly become quite a performance drag. It's also a major issue if you're using a server back end, since all the rows will have to be pulled across the wire, instead of Access/Jet/ACE being able to hand off the selection to the server.