Another SQL question (2k)

Topic

New Reply

We recently upsized an Access database to SQL Server. It was not as bad as I thought it would be, and all the forms and reports and queries, etc. work. However, there is one issue that remains unresolved and I respectfully request guidance on how to address it. In access we stored the date and time in separate fields. When we upsized we tried to do the same. We are using ASP to get the data into the SQL tables, but have not been able to store a time. It keeps stating that the data is in the wrong format. We have tried 24 hour format (13:00) as well as AM/PM (1:00 PM), with no luck. Is there an easy solution. What am I overlooking?

Thanks!

Ken

Reply | Quote

Replies

SQL Server stores dates in a different internal format than Access, and as a result you run into these kind of issues. You should be able to store dates without a time – it assumes you mean midnight (if you put the data into a date/time column in SQL Server). The time however is a challenge. Is there a specific reason you need to separate the time from the date?

Reply | Quote

Wendell,

Thanks for the followup. The reason for storing the date and time separately is the customer has become conditioned (or accustomed) to them being separate. They really think in terms of a single field storing both date and time. Some of the time fields do not necessarily relate to a specific date and therefore would have a time but no date. Does that make sense? For now we are looking at switching the time fields from datetime to strings.

SQL has more challenges and caveats then I had expected.

Thanks for ideas.

Ken

Reply | Quote

Have you by chance made the time field a timestamp type? That would cause trouble no doubt – it’s a special kind of field for providing time synchronization and not really a time field at all. It’s been some time since I looked at the way SQL Server handles dates, but I believe it shifts the position of the implicit decimal point one hex digit to the right, so that time is stored with a bit less precision than in Access and thus SQL Server can handle a few more years than Access. So it seems to me you should be able to upsize time fields stored in Access date/time format to a SQL Server table with a date/time field, and unless you need precision to the millisecond level, it should work.

FYI, Access will accept valid dates from January 1, 100 AD to December 31, 9999 AD. SQL Server will accept valid dates from January 1, 1753 AD to December 31, 9999 AD. Thus you can run into problems upsizing date/time fields to SQL because of that issue.

Reply | Quote

Have you by chance made the time field a timestamp type? That would cause trouble no doubt – it’s a special kind of field for providing time synchronization and not really a time field at all. It’s been some time since I looked at the way SQL Server handles dates, but I believe it shifts the position of the implicit decimal point one hex digit to the right, so that time is stored with a bit less precision than in Access and thus SQL Server can handle a few more years than Access. So it seems to me you should be able to upsize time fields stored in Access date/time format to a SQL Server table with a date/time field, and unless you need precision to the millisecond level, it should work.

FYI, Access will accept valid dates from January 1, 100 AD to December 31, 9999 AD. SQL Server will accept valid dates from January 1, 1753 AD to December 31, 9999 AD. Thus you can run into problems upsizing date/time fields to SQL because of that issue.

Reply | Quote

Wendell,

Thanks for the followup. The reason for storing the date and time separately is the customer has become conditioned (or accustomed) to them being separate. They really think in terms of a single field storing both date and time. Some of the time fields do not necessarily relate to a specific date and therefore would have a time but no date. Does that make sense? For now we are looking at switching the time fields from datetime to strings.

SQL has more challenges and caveats then I had expected.

Thanks for ideas.

Ken

Reply | Quote

If the time itself is stored in Access without a date, and you use a datetime field, you’re really storing the time on the default date. When we need a standalone time, say for the start of a shift, we store it in HH:NN format as a string. Then some simple date math can be used to add the time to a particular day when we need a full date time for calculations. We use that in both Access and SQL Server.

Reply | Quote

Charlotte,

That sounds like a workable plan! Thanks for all your help.

Ken

Reply | Quote

Charlotte,

That sounds like a workable plan! Thanks for all your help.

Ken

Reply | Quote

If the time itself is stored in Access without a date, and you use a datetime field, you’re really storing the time on the default date. When we need a standalone time, say for the start of a shift, we store it in HH:NN format as a string. Then some simple date math can be used to add the time to a particular day when we need a full date time for calculations. We use that in both Access and SQL Server.

Reply | Quote