December 1, 2011 at 6:27 pm
I am having a bit of trouble trying to import a datetime field from MS Access into SQL Server 2005 database.
The problem that I am having is when I import the datetime from MS Access into SQL Server, the milliseconds information got cut off.
For example, in MS Access, I have '2011-01-02 07:58:21.090', after import into SQL Server, it only shows '2011-01-02 07:58:21.000'. I know the milliseconds information is there in MS Access even through when I open the table in MS Access, it does not show the milliseconds, but when I copy the datetime value from MS Access into Excel, I can see the milliseconds information in Excel.
So my question is, how can I import the milliseconds information from MS Access into SQL Server?
MS Access is on 2003.
December 2, 2011 at 2:46 am
Are you using the Import Data Wizard?
December 2, 2011 at 10:13 am
Yes, I tried Import Wizard and also create a linked server to the mdb file and query the table directly.... still not seeing the milliseconds.
December 2, 2011 at 10:28 am
dcw8888 (12/2/2011)
Yes, I tried Import Wizard and also create a linked server to the mdb file and query the table directly.... still not seeing the milliseconds.
In Import Wizard you can map the columns with different data types. Verify the default data type there & update it per your need.
December 2, 2011 at 2:01 pm
There are only smalldate, timestamp and datetime for the datetime format. Tried them all, did not make a difference.... I still don't see the milliseconds after the import.
December 2, 2011 at 2:54 pm
What is the Date Format of the column in Microsoft Access.
Access 2007 does not store milliseconds and I was not aware that Access 2003 did either.
http://office.microsoft.com/en-us/access-help/format-date-and-time-values-HP001099015.aspx[\url]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 2, 2011 at 4:26 pm
The data type in Access is "Date/Time". Yes, at first I thought Access does not store the milliseconds information as well because when I open the table up in Access, I don't see milliseconds and when using SQL tap into the Access, I don't see milliseconds as well. But if I copy the Date/Time column in Access and paste it in Excel, I can see the milliseconds information.
This is why I know Access is storing the milliseconds information but I just can't export it to SQL Server.
December 2, 2011 at 4:56 pm
dcw8888 (12/2/2011)
The data type in Access is "Date/Time". Yes, at first I thought Access does not store the milliseconds information as well because when I open the table up in Access, I don't see milliseconds and when using SQL tap into the Access, I don't see milliseconds as well. But if I copy the Date/Time column in Access and paste it in Excel, I can see the milliseconds information.This is why I know Access is storing the milliseconds information but I just can't export it to SQL Server.
Could you copy and paste the DateTime value into this thread so I can see what you have?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 2, 2011 at 7:43 pm
dcw8888 (12/2/2011)
The data type in Access is "Date/Time". Yes, at first I thought Access does not store the milliseconds information as well because when I open the table up in Access, I don't see milliseconds and when using SQL tap into the Access, I don't see milliseconds as well. But if I copy the Date/Time column in Access and paste it in Excel, I can see the milliseconds information.This is why I know Access is storing the milliseconds information but I just can't export it to SQL Server.
I have tested this and the result differs from what you have stated.
You may want to reconsider your assumption.
What is the format of the Access Date/Time Field and the Excel Field?
Excel does not have a format that supports milliseconds.
You may want to revisit what you have done and what you have stated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 3, 2011 at 3:29 am
I think Welsh is right! 🙂
December 3, 2011 at 9:52 am
I came across the following article:
MS Access can retrieve and measure time with millisecond precision, but only with the help of a few well-known API calls and several user-defined functions.
http://www.devx.com/dbzone/Article/39046/1954
I also found out that you can assign a custom format to include milliseconds:
mm/dd/yyyy hh:mm:ss.000
I experimented with in Excel and I got zeros on the hh:mm:ss.000 part of the datetime.
You should probably do a little research on the article extracting milliseconds from Access.
Why do yo need milliseconds? What type of application is it?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 3, 2011 at 3:43 pm
Welsh Corgi (12/3/2011)
I came across the following article:MS Access can retrieve and measure time with millisecond precision, but only with the help of a few well-known API calls and several user-defined functions.
http://www.devx.com/dbzone/Article/39046/1954
I also found out that you can assign a custom format to include milliseconds:
mm/dd/yyyy hh:mm:ss.000
I experimented with in Excel and I got zeros on the hh:mm:ss.000 part of the datetime.
You should probably do a little research on the article extracting milliseconds from Access.
Why do yo need milliseconds? What type of application is it?
Hi Welsh,
Yes, in Excel, you need to put in a custom datetime format in order for it to show the milliseconds information.
That's the custom format you need to set on the cell in order to get it to show milliseconds.
mm/dd/yyyy hh:mm:ss.000
Thanks for the link and I will look into it when I am back to my office on Monday.
December 3, 2011 at 7:19 pm
What format are you using in Access?
Could you provide the steps to reproduce?
What happens when you export from Access to Text?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 5, 2011 at 10:55 am
Welsh Corgi (12/3/2011)
What format are you using in Access?Could you provide the steps to reproduce?
What happens when you export from Access to Text?
In Access, the field Data Type is "Date/Time"
To reproduce it, in SQL Server, create a table with a column data type Datetime then just insert "Select getdate()" to the field and you should get the millisecond information. Using Access, extract the data from the SQL Server.
Once in Access, open the table and you should see the datetime information but without the milliseconds. Copy the datetime from Access and paste it in Excel and change the format of the cell and you should see the milliseconds information.
When just exporting from Access to Text, it is still not exporting the milliseconds.
December 5, 2011 at 11:08 am
Thanks DCW, I know how to create a DateTime Field and populate it. 😀
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply