Storing Time value in Access
-
Lately I've been working an C# app that stores values in an Access db. One of the fields stores a time value. (7:30 PM) Is there a more effecient way to store a time value than using the DateTime column? I will need to convert it to a DateTime value in the SELECT query. Serious answers please. :suss:
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best Friend -
Lately I've been working an C# app that stores values in an Access db. One of the fields stores a time value. (7:30 PM) Is there a more effecient way to store a time value than using the DateTime column? I will need to convert it to a DateTime value in the SELECT query. Serious answers please. :suss:
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best FriendI'm a bit confused. :confused: You say that want a better way to store time in Access, but you will need to convert it to DateTime in your select statement. Then why not store it in a DateTime column to begin with ? I worked for a Time and Attendace company for a while and what I've learned is that you always seem to need the date component along with the time because there is always the overnight shift worker. (11pm to 7am) When you store the data in a DateTime column, the calculation of time difference is already built into those functions, you don't have to worry about crossing midnight etc. My vote is to store the time in a DateTime column. :thumbsup: I'm getting off my soapbox now. ;) Best of luck with your project.
-
I'm a bit confused. :confused: You say that want a better way to store time in Access, but you will need to convert it to DateTime in your select statement. Then why not store it in a DateTime column to begin with ? I worked for a Time and Attendace company for a while and what I've learned is that you always seem to need the date component along with the time because there is always the overnight shift worker. (11pm to 7am) When you store the data in a DateTime column, the calculation of time difference is already built into those functions, you don't have to worry about crossing midnight etc. My vote is to store the time in a DateTime column. :thumbsup: I'm getting off my soapbox now. ;) Best of luck with your project.
The key word here is "store". Access has a max capacity of 2gb. DateTime types require 8 bytes for each field. Some Access developers have advocated using an int type for Date only values. This would be accomplished by storing the number of days since 1900 and using the DateAdd() function to convert the value to DateTime in the SELECT query. I ask for serious responses! :-D
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best Friend -
The key word here is "store". Access has a max capacity of 2gb. DateTime types require 8 bytes for each field. Some Access developers have advocated using an int type for Date only values. This would be accomplished by storing the number of days since 1900 and using the DateAdd() function to convert the value to DateTime in the SELECT query. I ask for serious responses! :-D
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best FriendRichard Blythe wrote:
I ask for serious responses!
Use a DateTime-field - that's what most people do, and that's where you can expect the most support. You can fit a whole lotta dates in those 2 Gb, and optimizing for space is a bit of a timewaster IMO.
I are Troll :suss:
-
Richard Blythe wrote:
I ask for serious responses!
Use a DateTime-field - that's what most people do, and that's where you can expect the most support. You can fit a whole lotta dates in those 2 Gb, and optimizing for space is a bit of a timewaster IMO.
I are Troll :suss:
Wow that's pretty serious! :) I certainly don't want to create a second curse by trying to eliminate the first one. Thanks for the reply.
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best Friend -
The key word here is "store". Access has a max capacity of 2gb. DateTime types require 8 bytes for each field. Some Access developers have advocated using an int type for Date only values. This would be accomplished by storing the number of days since 1900 and using the DateAdd() function to convert the value to DateTime in the SELECT query. I ask for serious responses! :-D
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best Friend -
The key word here is "store". Access has a max capacity of 2gb. DateTime types require 8 bytes for each field. Some Access developers have advocated using an int type for Date only values. This would be accomplished by storing the number of days since 1900 and using the DateAdd() function to convert the value to DateTime in the SELECT query. I ask for serious responses! :-D
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best FriendI agree with Mr Gray, if you have to optimise for space using spit Access then you are using the wrong tool. Seriously change tools, there are many better options out there than spit Access
Never underestimate the power of human stupidity RAH
-
Lately I've been working an C# app that stores values in an Access db. One of the fields stores a time value. (7:30 PM) Is there a more effecient way to store a time value than using the DateTime column? I will need to convert it to a DateTime value in the SELECT query. Serious answers please. :suss:
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best FriendI agree with the others, except when the application would fit the following description: - a lot of fields are time values, so storing them in a compact format will be significant to DB size; - the time resolution and range required are such that a small integer could do, say 1-minute resolution and a 2-day range (to cope with midnight shifts), would fit in a 16-bit int; - your DB queries are known in advance and don't require complex datetime calculations. If there is start time and end time, I would consider storing begin time and duration instead. If the above matches pretty well, then you're set for a smaller DB, and no installation, as the JET engine is always present AFAIK. OTOH if the above feel like restrictions, if there is too much uncertainty about how the requirements will evolve, if you want the comfort of full T-SQL, ... then by all means use a real DB and pay the price. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
I agree with the others, except when the application would fit the following description: - a lot of fields are time values, so storing them in a compact format will be significant to DB size; - the time resolution and range required are such that a small integer could do, say 1-minute resolution and a 2-day range (to cope with midnight shifts), would fit in a 16-bit int; - your DB queries are known in advance and don't require complex datetime calculations. If there is start time and end time, I would consider storing begin time and duration instead. If the above matches pretty well, then you're set for a smaller DB, and no installation, as the JET engine is always present AFAIK. OTOH if the above feel like restrictions, if there is too much uncertainty about how the requirements will evolve, if you want the comfort of full T-SQL, ... then by all means use a real DB and pay the price. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
Luc Pattyn wrote:
then by all means use a real DB and pay the price.
I would love to use SQL Server Express but the software is being deployed with CD-ROM using InstallShield 2010 express edition. This edition cannot install SQL Server silently. (Which would be a must) At this point I can't justify the $900 upgrade cost on InstallShield so Access seems to be my best bet. :sigh:
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best Friend -
Lately I've been working an C# app that stores values in an Access db. One of the fields stores a time value. (7:30 PM) Is there a more effecient way to store a time value than using the DateTime column? I will need to convert it to a DateTime value in the SELECT query. Serious answers please. :suss:
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best FriendI guess the problem here is not the field type in Access - in almost all cases it must be DateTime - but the user interface. Make sure you can convert a user input like "7:30 PM" correctly into a DateTime value. And that you format values returned from the database in a way the user wants to see them. For the communication with the database, you must use a parameterized query when you want to send DateTime values from your application to the database - be certain to add the DateTime values as DateTime values, and do not convert them to strings when adding them!
-
Lately I've been working an C# app that stores values in an Access db. One of the fields stores a time value. (7:30 PM) Is there a more effecient way to store a time value than using the DateTime column? I will need to convert it to a DateTime value in the SELECT query. Serious answers please. :suss:
Be nice to your kids. They'll choose your nursing home.
My Articles Developer's Best FriendWe have saved the 24 hr time in a text field as HH:MM That being said you need to look at what overhead is needed to convert it to a true time. By the way Microsoft Access and serious do not go together. :)