Solved

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

Posted on 2013-05-31
14
542 Views
Last Modified: 2013-07-31
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
Comment
Question by:stephenlecomptejr
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39212404
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39212412
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39212425
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
 
LVL 49

Expert Comment

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

/gustav
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39213070
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39213103
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39213210
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39213303
Looking for an Access developer's comment here!
Fair enough.  I will back out then.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39213658
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39214153
> 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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39214259
@ 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
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 150 total points
ID: 39214269
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 350 total points
ID: 39214272
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39223555
I'm going to implement this suggestion this Friday - please be patient with me.
Thank you for all your replies thus far!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now