Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3037
  • Last Modified:

DateTime2 to Date

It's been a number of years since I've had the opportunity to use SQL Server, so am slowly getting my feet wet again.  Client has a SQL Server table that contains 12M records and date field of type DateTime2.

I need to be able to link to this table from within Access, but Access sees the field as text.  

Are there any issues with simply changing the datatype of the SQL Server field from DateTime2 to Date?  

Can I do that in table design view, or should I run an Alter Table query?  If the latter, what would be the appropriate syntax to change [docDate] from DateTime2 to Date
0
Dale Fye
Asked:
Dale Fye
  • 3
  • 2
  • 2
  • +2
1 Solution
 
ZberteocCommented:
In the SQL table create a calculated column that will have as value the original one casted to date. So let's say yor column is coldate_datetime2, you will create a new one called coldate_date and in the formula you put CAST(coldate_datetime2 as date). In Access you will use the new column even though you will be able to see them both.
0
 
Gustav BrockCIOCommented:
> .. any issues with simply changing the datatype of the SQL Server field from DateTime2 to Date?  

No, except that you will fall back to the resolution of ~3ms of DateTime but that is probably not an issue for you in Access.

Using Cast is an option but that will, of course, make that column read-only in Access.

Third option is to leave the field as is, and then convert to and from Text using Cdate when reading and Format(datDate, "yyyy\/mm\/dd hh\:nn\:ss") when writing.

/gustav
0
 
ZberteocCommented:
Date type has no time associated to it so the ms are not a factor here.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kseaboltCommented:
You should always know the reason a datatype has been selected before changing it.  AFAIK, DateTime2 could be chosen to add nanosecond precision to the values stored and to allow for storage of dates with years prior to 1753.  Before altering the SQL table to accommodate what you are doing in Access, you might consider making the needed adjustments in Access instead, rather than muck with the source?

I had a similar experience and figured out in my case that all I needed to do was change the DSN from SQL Server to the current SQL Server Native Client 10.0   With the older ODBC driver the datetime2 column appears as text but with the newer 10.0 driver it appears as date/time.  HTH
0
 
kseaboltCommented:
BTW, even the latter might not allow joins b/c in SQL Server the values are accurate to nanoseconds and stored as such, whereas the linked table has these as date/time and the linked values might still not have full precision.  i have not tested that.
0
 
Dale FyeAuthor Commented:
gustav,

I've been experimenting with another table.  Migrated it to SQL Server and it created another DateTime2 field from an Access DateTime field, but when I linked it back into Access, it again showed up as a text datatype in the linked table.

I then converted that field to a Date datatype in the SQL Server table, and relinked the table to Access and it still shows up as a text field.

I was hoping to use this field in a join, and I know I can do a non-equi join in Access and use the Format function to get the Access date field into the appropriate format, but why won't Access recognize a Date Field in SQL Server as a date?
0
 
als315Commented:
No points!
fyed, I've just tried to change driver from SQL to SQL server native client 11 as it was recommended by kseabolt and all fields (date, datetime and datetime2) are now date/time in Access.
0
 
Dale FyeAuthor Commented:
Perfect.

The data in the SQL Server table was originally migrated from Access, and only really needed Date level accuracy.  Changing the connection string worked perfectly, and I'm now able to join on that table without any problems.

Wonder why the SQL Migration Assistant used the "SQL Server" connection rather than the later version.
0
 
Gustav BrockCIOCommented:
> Wonder why the SQL Migration Assistant used the "SQL Server" connection ..

That may be because this works with other ports than 1433 of SQL Server.
Don't know about version 11. Where do you obtain that?

/gustav
0
 
als315Commented:
I have Visual Studio 11 Beta installed. May be version 11 was installed with it?
0
 
Gustav BrockCIOCommented:
It is called:

Microsoft® SQL Server® 2012 Native Client

and is for download here:

http://www.microsoft.com/en-us/download/details.aspx?id=29065

/gustav
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now