CODEWITHSUNDEEP
sql-serverms-access
Gold
Gold

Reputation: 62464

about date in database question

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

Upvotes: 1

Views: 102

Answers (3)

David-W-Fenton
David-W-Fenton

Reputation: 23067

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.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

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.

Upvotes: 1

anishMarokey
anishMarokey

Reputation: 11397

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

Upvotes: 0

Related Questions