x

Dates in SQL 2008 backend and Access Frontend

I am migrating data from Access to SQL Server 2008 and keeping for now the Access frontend.

I'm finding that dates in the Access frontend are now showing as YYYY/MM/DD rather than the reverse.

I'm assuming its because the SQK Server backend is english language and the dates are stored that way.

Anyway, is there any way to change this on the SQL backend without changing the Access frontend ?

CSB
more ▼

asked Feb 07, 2011 at 12:04 PM in Default

csb gravatar image

csb
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You can use the command SET DATEFORMAT to make that happen. Here is the relevant [Books Online entry][1].

[1]: http://msdn.microsoft.com/en-us/library/ms189491.aspx
more ▼

answered Feb 07, 2011 at 07:31 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
97.9k 19 21 74

(comments are locked)
10|1200 characters needed characters left

Another option would be to use [CONVERT][1] in the select statement/view that is accessing the data:

SELECT  GETDATE(),                            -- 2011-02-08 08:55:36.803
        CONVERT(varchar(100), GETDATE(), 101),-- 02/08/2011
        CONVERT(varchar(100), GETDATE(), 102),-- 2011.02.08
        CONVERT(varchar(100), GETDATE(), 103),-- 08/02/2011
        CONVERT(varchar(100), GETDATE(), 0)   -- Feb  8 2011  8:55AM
[1]: http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx
more ▼

answered Feb 07, 2011 at 11:50 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

Actually the best answer to this question is that when using the SSMA Migration aid (as I am doing and failed to mention above) you need to specify DateTime for Access Date field conversions in the data mapping section.

If you use date or datetime2 or any other data mapping the date fields come over to sql as dates but appear to Access as text. That's why they are formatted YYYY/MM/DD rather than the reverse as they were in Access frontend and backend. They are not only backwards but can't be used in date calculations as well.

SSMA is a great migration tool but there are some differences to note on the default field type mappings.
more ▼

answered Feb 09, 2011 at 11:50 AM

csb gravatar image

csb
13 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1832
x94
x27
x7

asked: Feb 07, 2011 at 12:04 PM

Seen: 1552 times

Last Updated: Feb 07, 2011 at 12:04 PM