tags:

views:

37

answers:

1
+1  Q: 

MS-Access front-end does not recognise dates from SQL Server

I have a SQL Server 2008 database with an Access front-end. My problem is that Access does not recognise SQL Server's dates as they are in a different format.

SQL Server-s format is YYYY-MM-DD
Access' format is DD-MM-YYYY

When the date is displayed in a text-box, it is displayed as a string (without the little calendar icon next to it).
Is there anyway I can configure my Access front-end so that it recognises SQL Server's dates?

Cheers.

+1  A: 

If you are storing the dates in SQL server as the data type “Date” or “Date2” try changing them to “DateTime” I had this problem linking data from SQL server 2008R2 to access 97, access did not see it as a date and treated it like text

Kevin Ross  2010-09-14 07:15:17
You are a gentleman and a scholar.
billynomates  2010-09-14 11:05:33
Another thing that isn't involved here is if you upsize Access data to SQL Server using SQL Server Migration Assistant, any date column with values that are invalid in SQL Server will be created in SQL Server as text (!!!). SSMA allows you to run a report before the upsizing, so it will flag the tables that have invalid dates (though it won't tell you which fields). I'm just throwing this in for completeness because I encountered it just in the last 10 days.
David-W-Fenton  2010-09-14 20:28:38

related questions

Handling and storing elapsed time
How do I convert local time to UTC in Python?
Mono's DateTime Serialization
Best practices for DateTime serialization in .Net framework 3.5/SQL Server 2008
Parsing exact dates in C# shouldn't force you to create an IFormatProvider
C# Casting vs. Parse
.NET Date Const (with Globalization)
Dynamic regex for date time formats
What is the simplest way to find the difference between 2 times in python?
Best way to convert DateTime to "n Hours Ago" in SQL
What's a good way to overwrite DateTime.Now during testing?
Datetime arithmetic with a string in Ruby
How can I get the DateTime for the Start of the Week?
Is DateTime.Now the best way to measure a function's performance?
SQL Server DateTime conversion failure
Natural language date/time parser for .NET?
What's a good way to check if two datetimes are on the same calendar day in TSQL?
Why does this python date/time conversion seem wrong?
Dealing with PHP server and MySQL server in different time zones
How do I parse and convert DateTime's to the RFC 3339 date-time format?
HOWTO - Compare a date string to datetime in SQL Server?
Best method for varchar date validation in Sybase (T-SQL)?
What's the BEST way to remove the time portion of a datetime value (SQL Server)
How do I calculate relative time?
How do I calculate someone's age in C#?