Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-04-2018, 07:28 AM   #1
rvsebi
Newly Registered User
 
Join Date: Jun 2015
Posts: 67
Thanks: 7
Thanked 1 Time in 1 Post
rvsebi is on a distinguished road
Data type in SQL Back End

Hi, I used SSMA to migrate my access back end into SQL server and i linked tables from SQL to my access front end.
All my columns from access tables with data type date() now are datetime2.
I changed all datetime2 in date so now i have something like 2018-07-04. How can i change style of date to look like 04.07.2018 ( i saw that style is 104 - german here https://www.w3schools.com/sql/func_s...er_convert.asp) ?
There is any way to change that style automatically in sql server regional or something?

Thank you in advance!

rvsebi is offline   Reply With Quote
Old 07-04-2018, 07:52 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,141
Thanks: 0
Thanked 683 Times in 668 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Data type in SQL Back End

I would say its a field property on the backend.

Tho you could try format() function on the Frontend,
But BE is better.
Ranman256 is offline   Reply With Quote
Old 07-04-2018, 07:54 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,714
Thanks: 138
Thanked 1,536 Times in 1,508 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Data type in SQL Back End

Change them all to DateTime datatype and In Access , it should revert to your regional settings.

In SQL Server dates are normally displayed yyyy-mm-dd and entered as criteria in the same fashion.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 07-04-2018, 08:03 AM   #4
rvsebi
Newly Registered User
 
Join Date: Jun 2015
Posts: 67
Thanks: 7
Thanked 1 Time in 1 Post
rvsebi is on a distinguished road
Re: Data type in SQL Back End

I used already field property to change from datetime2 to date but now looks like 2018-07-04 and i want to change to 04.07.2018. If i use new query on sql server with "SELECT convert(varchar,Column_name,104) from dbo.Table_name" i see exactly what i want. Now question is how i can change data in table to look like in the "select convert ... ". Thank you!
rvsebi is offline   Reply With Quote
Old 07-04-2018, 08:06 AM   #5
rvsebi
Newly Registered User
 
Join Date: Jun 2015
Posts: 67
Thanks: 7
Thanked 1 Time in 1 Post
rvsebi is on a distinguished road
Re: Data type in SQL Back End

Minty in access front end i cant change data type in linked table. I would like to do that but access tell me when i open table in design i cant change nothing there ... and more then this all columns with datatype date in sql have datatype text in access ??? Maybe access cant understand the datatype date from sql and transform this in string ( Text)

Last edited by rvsebi; 07-04-2018 at 08:13 AM.
rvsebi is offline   Reply With Quote
Old 07-04-2018, 08:43 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,714
Thanks: 138
Thanked 1,536 Times in 1,508 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Data type in SQL Back End

Right take a step back.

When I said change the data type to DateTime I meant change it in SQL Server.
You can't change how it is displayed natively in SQL Server. You can change it for presentation purposes using a Convert() function, but you don't need to if you change the SQL data type to DateTime.

Access will deal with the DateTime SQL data type correctly, and should display it according to your windows / regional preferences.

Does that make sense?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
rvsebi (07-04-2018)
Old 07-04-2018, 08:51 AM   #7
rvsebi
Newly Registered User
 
Join Date: Jun 2015
Posts: 67
Thanks: 7
Thanked 1 Time in 1 Post
rvsebi is on a distinguished road
Re: Data type in SQL Back End

Quote:
Originally Posted by Minty View Post
Right take a step back.

When I said change the data type to DateTime I meant change it in SQL Server.
You can't change how it is displayed natively in SQL Server. You can change it for presentation purposes using a Convert() function, but you don't need to if you change the SQL data type to DateTime.

Access will deal with the DateTime SQL data type correctly, and should display it according to your windows / regional preferences.

Does that make sense?
Half of problem solved, ty
Now Access show me 04.07.2018 0:00:00. How can i get rid off this 0:00:00 ?

rvsebi is offline   Reply With Quote
Old 07-04-2018, 08:55 AM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,714
Thanks: 138
Thanked 1,536 Times in 1,508 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Data type in SQL Back End

Simply format the query / control to display short date.
All dates are actually stored as DateTimes you just don't normally see the time portion.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 07-04-2018, 09:07 AM   #9
rvsebi
Newly Registered User
 
Join Date: Jun 2015
Posts: 67
Thanks: 7
Thanked 1 Time in 1 Post
rvsebi is on a distinguished road
Re: Data type in SQL Back End

Quote:
Originally Posted by Minty View Post
Simply format the query / control to display short date.
All dates are actually stored as DateTimes you just don't normally see the time portion.
I already have shortdate in all my controls with date datatype on all access forms. Still when i bring data to form i see 04.07.2018 0:00:00. I miss something maybe
rvsebi is offline   Reply With Quote
Old 07-05-2018, 12:18 AM   #10
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,714
Thanks: 138
Thanked 1,536 Times in 1,508 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Data type in SQL Back End

The easiest route might be to create a query against the table returning all fields, and format the output for any date fields in that and use that as your main table source.

There are some perceived performance improvements in using this method, depending on your environment.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 07-05-2018, 01:02 AM   #11
rvsebi
Newly Registered User
 
Join Date: Jun 2015
Posts: 67
Thanks: 7
Thanked 1 Time in 1 Post
rvsebi is on a distinguished road
Re: Data type in SQL Back End

Problem solved. I did it all again from the begining in this order
1. migrate all tables from access back end to sql server with ssma
2. edit all datatype datetime2() in datetime
3. create odbc to link tables from sql server to access front end
4. rename all linked tables from dbo_TableName in TableName
5. insert dbSeeChanges in all my recordset calls
That is all i do and program work very well.

The result: now in access all linked tables with datatype Date are declared like Date\Time automatically and ofcourse style of Date is like i wanted because of windows regionals ( 05.07.2018)
I think before didnt worked because i changed datatype from datetime2 to datetime after i linked tables from sql, wrong order.
Thank you all for your time!!
rvsebi is offline   Reply With Quote
Old 07-05-2018, 01:21 AM   #12
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,714
Thanks: 138
Thanked 1,536 Times in 1,508 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Data type in SQL Back End

Glad you resolved it.

Sometimes just deleting and relinking after updating the backend tables will sort out these issues, it's probably worth setting up a method to do this in VBA if you are likely to do a lot of development.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 07-07-2018, 02:28 AM   #13
rvsebi
Newly Registered User
 
Join Date: Jun 2015
Posts: 67
Thanks: 7
Thanked 1 Time in 1 Post
rvsebi is on a distinguished road
Re: Data type in SQL Back End

One more problem i found(for others to know).
If u gonna have update query with linked table check those tables on sql server to have primary key before u do the link, otherwise u will get an error something like "Operation must use an updateable table" If u did already the link, delete it modify table on sql and link again
rvsebi is offline   Reply With Quote
Old 07-08-2018, 11:51 PM   #14
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,714
Thanks: 138
Thanked 1,536 Times in 1,508 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Data type in SQL Back End

You must define a unique key with SQL linked tables, otherwise the ODBC Driver can't keep track of which records are being updated.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing field data type but keeping data Kozbot Tables 2 10-23-2013 12:20 PM
Need help converting text data type to number data type elvsmart Tables 7 11-03-2012 02:20 PM
Operand type clash: Date inconsistent with float, data type mismatch Zeke Modules & VBA 8 05-13-2010 09:18 AM
excel data import - data type issues garethl Excel 11 06-28-2007 02:28 AM




All times are GMT -8. The time now is 07:54 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Is Political Correctness Toxic?

Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World