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

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.

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?

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

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

/gustav

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

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.

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

Looking for an Access developer's comment here!
Fair enough.  I will back out then.

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

> 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

@ 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?

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