how to address datetime fields from SQL backend in Access front end?

Question

I've used SSMA to migrate my access backend to SQL Server 2014. I've updated all my forms, queries and reports to work with SQL. I knew from the beginning that the datetime was showing up wrong in all of the forms (my users only wish to store date, not date AND time) and I assumed I'd easily be able to address the issue later on from the Access FE.

Now that I'm reaching the end of the project I haven't been able to find an easy way to convert the fields in the forms and reports to only show the date instead of the date AND time as its stored in SQL.

One thing I've tried that works, at least with a few reports that are based on SQL views (which I have in access as a linked table) is modify the view to convert the date e.g. convert(varchar,StartDate,101) as StartDate

Is there anyway to easily convert the DateTime (yyyy-mm-dd hh:mm:ss) fields from SQL to show up in access as just Date (mm/dd/yyyy)?

@Mat'sMug He might not want to or be able to change the SQL view itself, in which case the forms just need a format function: format("2016-12-09 08:40:20", "yyyy-mm-dd") — Tom Malkin
– Tom Malkin, Commented Dec 9, 2016 at 4:20

Gustav · Accepted Answer · 2016-12-09 07:19:12Z

1

You just have to set the Format property of the textbox holding the date value:

mm/dd/yyyy

or simply Short Date which, however, is localized.

This, of course, only works if your data type in SQL Server is DateTime, not DateTime2 which the ODBC driver returns as text.

answered Dec 9, 2016 at 7:19

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Ryan Over a year ago

Thanks for your reply guys. I tried your suggestion @Gustav and it didn't appear to make a difference. in the format property of a random date text box I tried "mm/dd/yyyy" as well as the "short date" setting and they both resulted in the date and time still showing. In fact slightly worse since now it seems its right justified so that just the time is visible until you select left ( couldn't get ctrl+i to work to insert an image...) I am using ODBC with "SQL Server" for the driver.

Ryan Over a year ago

I also felt it important to note that casting won't help for forms that are based off of a table. I would hate to have to create a view for every form that displays a date!

Gustav Over a year ago

If the "dates" won't format, they are text, meaning that you most likely have defined the field in SQL Server as DateTime2 and not DateTime. You will have to correct this or anywhere in your application you will have to convert between Date and Text and viceversa. Not fun.

Ryan Over a year ago

Ahh crud, well I didn't explicitly define them, SSMA I believe would have done that... I'm checking now.

Ryan Over a year ago

DANG! You're right!!! I didn't even catch that, they're ALL DateTime2, NOO!!!!!!!!!! Time to put my head down... Thanks for the help

|

Collectives™ on Stack Overflow

how to address datetime fields from SQL backend in Access front end?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related