locked
Date Conversion From SQL Server To Access Database RRS feed

  • Question

  • When importing a table through ODBC link from Microsoft SQL Server into Microsoft Access, the field named LKL_DATE is converted from a format displayed in the user interface of Military Zulu time which uses a format of ddhhhhZmmmYYYY to a strictly numerical format when viewed from the table in Microsoft Access.  Below is an example.   My questions are what format is the data being converted to between the SQL server and being viewed in Microsoft Access.  The second is how do I convert the field data in Microsoft Access back to a date time format.  Below is an example.

    Field: LKL_DATE

    Format in application GUI: 061002ZMar2011

    Format when Imported into Microsoft Access Table: 1299405720

    Thursday, March 17, 2011 5:20 PM

Answers

  • but you have forgotten to tell which is the datatype for this date in the Access database ( DateTime,string,integer...?).

    It took time, but he already answered this: It is store in a INT on SQL Server back-end. But after doing some math on the hopefully correct assumptaion that these values represent the same date:

    DECLARE @int BIGINT = 1300358220 ;
    DECLARE @date DATETIME = { ts '2011-03-23 10:02:00'} ;
    
    I think it is some special storage format, thus JBLT83 should ask the people who created the database how to do the calculus.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Proposed as answer by Papy Normand Thursday, March 24, 2011 6:21 PM
    • Marked as answer by Ai-hua Qiu Sunday, March 27, 2011 4:24 AM
    Thursday, March 24, 2011 9:54 AM

All replies

  • hi,

    What data type has this column on SQL Server?  What does a simple

    SELECT TOP 1 LKL_DATE FROM yourTable WHERE NOT LKL_DATE IS NULL ;
    
    return?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, March 17, 2011 6:25 PM
  • The workstation is configured to use transfer replication protocol (TRP) to obtain, parse and store the data in a local SQL express database on the computer.  How would I go about running this query or identifying what data type the column is defined as?   Can I use Microsoft Access to query and look at this or is there another application I could use?  When I view the table and this column in microsoft access after I've established the ODBC link to this table, it shows the column defined as a "Number" type.

    Saturday, March 19, 2011 9:06 PM
  • Huh?

    Simply inspect the table in SSMSE:

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

    And there you also run the query and you run this query in Access against your linked table and as a pass-through query.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Sunday, March 20, 2011 12:31 AM
  • Stefan,

    I went to view the column attributes for LKL_DATE in the table.  It is defined as an integer data type in the SQL server table.  When I ran the query you specified above it returned a value of 1300358220.  I'm trying to figure out how to convert this value back into a date format like it is displayed when viewed through the application user interface.    This will allow me to be able to build custom queries and reports for example if I want to run a query to show all records where last known location date (LKL_DATE) equals 23100ZMar2011.  The problem is I don't know what this date is equivalent to while it is being stored as an integer data type within the SQL server table.

    Please advise or if you could call me my office number is 229-639-7744

    Thanks,

    John Bacon

    Wednesday, March 23, 2011 3:07 PM
  • hi,

    so 1300358220 is 23100ZMar2011 which is what date/time in standard US or ISO format?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, March 23, 2011 3:56 PM
  • I believe ISO format using Greenwich mean time.


    V/R, John B.
    Wednesday, March 23, 2011 4:01 PM
  • hi,

    I mean, what is the time portion in 23100ZMar2011 and what the date portion? I've never seen a date/time formatted this way.

     


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, March 23, 2011 4:39 PM
  • Hello JBLT83,

    You said us in your 1st post that the format used to display the date is

     

    but you have forgotten to tell which is the datatype for this date in the Access database ( DateTime,string,integer...?).I am far to be a specialist of Access ( i stopped to use it since 2003 ) , but i think we will not be able to help you efficiently without this kind of information.

    Also, please, could you tell us the version of your ACCESS ( the year of the edition )? If I am not going wrong, i believe that there were the add of new types of data in each new version of ACCESS ( as for SQL Server ).

    Have a nice day

    PS : for Stefan Hoffmann, i think that

    231002ZMar2011 ==>

    year : 2011

    day : 23

    month : 03 (March )

    hour : 10:02

    But i have not understood how is translated a date with an hour related to afternoon or night ( before midnight )

    Maybe JBLT83 could give this information


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Thursday, March 24, 2011 9:06 AM
  • but you have forgotten to tell which is the datatype for this date in the Access database ( DateTime,string,integer...?).

    It took time, but he already answered this: It is store in a INT on SQL Server back-end. But after doing some math on the hopefully correct assumptaion that these values represent the same date:

    DECLARE @int BIGINT = 1300358220 ;
    DECLARE @date DATETIME = { ts '2011-03-23 10:02:00'} ;
    
    I think it is some special storage format, thus JBLT83 should ask the people who created the database how to do the calculus.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Proposed as answer by Papy Normand Thursday, March 24, 2011 6:21 PM
    • Marked as answer by Ai-hua Qiu Sunday, March 27, 2011 4:24 AM
    Thursday, March 24, 2011 9:54 AM
  • I need some brushing up on my SQL so how should the syntax look if I create a select query where I select one of these date columns i.e. the LKL_DATE column and include the DECLARE statement so that the results are returned showing the LKL_DATE column as a Date/Time format?
    V/R, John B.
    Monday, April 4, 2011 10:09 PM
  • hi,

    the problem is that your date/time value is encoded in some proprietary format, which I don't recognize nor which I can extract a value using simple date/time math. 

    So it's not your SQL knowledge, which needs a refresh, but your documentation of that column.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, April 5, 2011 8:52 AM
  • I see I'll have to try and find out from the DBAs what the proprietary format is that is being used.
    V/R, John B.
    Tuesday, April 5, 2011 12:04 PM
  • I found out that the date/time fields are stored as a standard unix timestamp within the database.  This means that the numbers I am seeing for example 1300358220 is the number of seconds since January 1, 1970.
    V/R, John B.
    Wednesday, April 6, 2011 6:23 PM
  • Hello,

    For SQL Server , you can use DECLARE @SqlServeDate datetime = DATEADD(seconds,@Seconds,'1970-01-01')

    where @Seconds is the integer filled with "zulu" date ( count of seconds since January 1,1970 )

    http://msdn.microsoft.com/en-us/library/ms186819(SQL.100).aspx

    But i let Stefan tol elaborate my proposal in a better way

    Have a nice day

     

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Wednesday, April 6, 2011 6:54 PM
  • How would I write the expression in a Microsoft Access query field so that the date fields so that the unix timestamp dates are converted and displayed into a standard date format like "mm/dd/yy hh:mm"?
    V/R, John B.
    Tuesday, May 17, 2011 12:22 PM