Improve company productivity with a Business Account.Sign Up

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

After transferring data from Access to SQL - how to get date and time fields formatted and look exactly as they had in Access?

Please note Image 1 that shows Start Date, End Date, Start Time and End Time and their values directly from the table as formatted.

In Image 2, this is how SQL Server has the data formatted within after using Microsoft SQL Server Migration Assistant for Access.

I need the SQL Server data to be formatted exactly as shown in Access.
I've tried changing the date type fields and then running the update query but the data value do not change.

Should I just change it to text as the data type values?
0
stephenlecomptejr
Asked:
stephenlecomptejr
  • 4
  • 3
  • 3
  • +2
2 Solutions
 
Brian CroweDatabase AdministratorCommented:
your attachments didn't come through.

Formatting is the job of the UI not the DB.  SQL stores the date as a BigInt I believe.  Don't have help handy but you can look up the datatype.  If you want it formatted a specific way you can use whatever front end is displaying it or the CONVERT function.
0
 
Brian CroweDatabase AdministratorCommented:
Expanding on my earlier post...look at

http://msdn.microsoft.com/en-us/library/ms187819.aspx

to get a better understanding of the datatypes.  If you give some specific examples of formats you are looking for I can show you how to use CONVERT.  What are you displaying the dates through?
0
 
Kelvin SparksCommented:
What version of SQL Server. From 2008(r2 I believe) you can use Date and Time data types to look more like access. By default you've probably ended up with a DateTime datatype.

Kelvin
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Gustav BrockCIOCommented:
Be sure to use data type DateTime in SQL Server, not DateTime2 as this will convert to text in Access.

/gustav
0
 
stephenlecomptejrAuthor Commented:
Here's Image 1 of Access the way they have their datetime fields shown as:

Note Image 2, the way SQL Server now has it.
The date fields in SQL Server are just date data type.
The time fields used the time data type.

"Formatting is the job of the UI not the DB."

True but I just transferred this over from Access back-end data to SQL Server and still use the Access front-end - I guess next time I need to just take all my sql statements in code and take the extra step of formatting such in the WHERE statement?  

But it would be nice to have SQL Server have similar data types for better transition so I wouldn't have to adjust several lines of code.

" If you give some specific examples of formats you are looking for I can show you how to use CONVERT.  "   I'm pulling all this in an Access front-end and have several sql statements using where [startdate] = #" & sDate & "#  - now I'm going to have to do a WHERE Format(StartDate, "mm/dd/yyyy") = #" & sDate & "#  everywhere in my code!

Before I go on this great journey - does not SQL Server have a data type that mirrors Microsoft Access?
Access-Date-Times.PNG
SQL-Server-Date-Times.PNG
0
 
Anthony PerkinsCommented:
does not SQL Server have a data type that mirrors Microsoft Access?
You are missing the point.  First lets dispel some myths.  Neither MS Access or MS SQL Server store dates in any format whatsoever.  In MS Access dates are stored as doubles and in MS SQL Server datetime and smalldatetime are stored as two integers.

Now that we have that out of the way, lets talk about the display.  In MS Access you set the formatting that you want in your application for every column in the design of the table.  MS SQL Server does not have an equivalent, unlike MS Access it is not involved with the front-end (presentation layer).  The best you can do from within SQL Server is use the CONVERT() functions to convert resultsets.

What you should not do or you will regret it later (and every subsequent developer working on your project will curse you for it) is to make the very naive mistake of converting to a character data type with the appropriate format.  There are a number of reasons why this is a very bad idea and I can elaborate if this is not abundantly clear to you.
0
 
stephenlecomptejrAuthor Commented:
For the date fields I have date data type and for the fields that store time - I have the time data type.

Should I change all to datetime and then in my Access front-end where VBA accesses the SQL Server data - use Format in my SQL statement via DAO Recordset to get the date to match up what the original Access was doing?

Looking for an Access developer's comment here!

Thank you,

Stephen
0
 
Anthony PerkinsCommented:
Looking for an Access developer's comment here!
Fair enough.  I will back out then.
0
 
Kelvin SparksCommented:
As an Access and SQL Developer (primarily Access), if you have migrated your data to SQL Server (any version) and connect your Access front end via ODBC (or OLEDB) The tables should show your date an time data in exactly the same format as the pre migration data.

You should be able to address date time fields in VBA in exactly the same manner as Access data. I assume from this question, that you are not find this to be the case. Can you please provide more data as to how you got to this point and what datatypes exist in both SQL Server and Access.


Kelvin
0
 
Gustav BrockCIOCommented:
> Should I change all to datetime ..

Yes. No Date, no Time, no SmallDate, no DateTime2

>.. then in my Access front-end where VBA accesses the SQL Server data -
> use Format in my SQL statement via DAO Recordset ..

No. Just retrieve the fields as is.

> .. to get the date to match up what the original Access was doing?

Use the Format property of your textboxes for this.

/gustav
0
 
stephenlecomptejrAuthor Commented:
@ Kelvin, at about comment 6 - I have visuals of it in the pics I provide.


=========================================

@  /gustav,

The only question I have further is when I do the following:


    sSQL = "INSERT INTO TEMPDELETETHESE ( LSID, EnrollStartDate, EventStartDate )"
    sSQL = sSQL & " SELECT TEMP.LSID, TEMP.EnrollStartDate, TEMP.EventStartDate"
    sSQL = sSQL & " FROM TEMP LEFT JOIN TEMPCOMPARE ON TEMP.[LSID] = TEMPCOMPARE.[LSID]"
    sSQL = sSQL & " WHERE (((TEMP.EventStartDate) < #" & sCurrentDate & "#) And ((TEMPCOMPARE.LSID) Is Null))"
    sSQL = sSQL & " ORDER BY TEMP.LSID;"
    lArea = 7
    CurrentProject.Application.CurrentDb.Execute sSQL

With the datetime stamp - it never finds the current date - so I'm going to have to format that in all my strings as well?   Correct?
0
 
Kelvin SparksCommented:
My only comment is not to compare what you see in SQL server SSMS with Access. Compare your data in Access with the pre converted data. Each has it own means of storage and display.

Kelvin
0
 
Gustav BrockCIOCommented:
No.

You could use:
sCurrentDate = Format(Date, "yyyy\/mm\/dd")

or much easier:
 sSQL = sSQL & " WHERE (((TEMP.EventStartDate) < Date()) And ((TEMPCOMPARE.LSID) Is Null))"

/gustav
0
 
stephenlecomptejrAuthor Commented:
I'm going to implement this suggestion this Friday - please be patient with me.
Thank you for all your replies thus far!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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