Results 1 to 9 of 9

Thread: Text to Time

  1. #1
    Join Date
    Mar 2004
    Posts
    11

    Unanswered: Text to Time

    I have an old database that has a field that records the time but is recording it in TEXT format. It is not currently specified as time because of the old handhelds we are using will not support it. When the handheld is connected and downloaded the time is recored in the text field in military format. These Oarcle tables are linked to an Access database that is pulling the information and the exporting it as text files which are then going to be imported into another program. The program wants the time in 00:00 format and I currently have it in 0000 format. I was wondering if any one knew how to change the format of the data during a query or after a query is run using SQL? Or does anyone know how to export the data in that format using Access or SQL.

    Example of Current Text Export
    Unit Date Time
    --------------------------------
    1000041 2004/2/17 1956
    1000047 2004/2/17 2038
    1000049 2004/2/17 2042

    Example of What I Need
    Unit Date Time
    ---------------------------------
    1000041 2004/2/17 19:56
    1000047 2004/2/17 20:38
    1000049 2004/2/17 20:42

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Text to Time

    In Oracle you can use TO_DATE with the format 'YYYY/MM/DD HH24MI' to convert the current text to a date, then use TO_CHAR with the format 'YYYY/MM/DD HH24:MI' to convert to a string again.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In SQL Server you could use:
    PHP Code:
    SELECT unitCAST (Left(a.tLen(a.t) - 2) + ':' Right(a.t2) AS datetime)
       
    FROM (SELECT 1000041 AS unit'2004/2/17 1956' AS t
       UNION ALL SELECT 1000047
    '2004/2/17 2038' 
       
    UNION ALL SELECT 1000049'2004/2/17 2042' ) AS 
    -PatP

  4. #4
    Join Date
    Mar 2004
    Posts
    11
    Thanks for the help but I am still having trouble. I will try to explain my situation a little bit better. I have a table called Report. In the table there is the following feilds.

    State Plate Number Date Make Time_In
    -------------------------------------------------------------
    ID test 1234 20040515 Ford 1024
    ID test 1222 20041216 Car 1824

    I need to runn a queary that will select all of the fields but change the time field to 10:24 instead of 1024. I think that the cast funciton may work if I do it right but I am getting errors. This is what I have so far

    Select state, plate, number, date, make, cast (time_in, len(time_in)-2 + Right(time_in, 2) AS time)
    FROM report;

    This is the first time I have tried to use cast and I am getting the error missing oporator in the query or expression.

    Any help would be great.

  5. #5
    Join Date
    Mar 2004
    Posts
    11
    The actual code is

    Select state, plate, number, date, make, cast (left, len(time_in)-2 + Right(time_in, 2) AS time)
    FROM report;

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, I missed the boat completely due to thinking you were running MS-SQL 2000 with MS-Access. If you are using Oracle, then I'd try using:
    PHP Code:
     SELECT TO_DATE([Date], 'YYYYMMDD')
    ,  
    TO_DATE([Time_In], 'HH24MI')
    ,  
    TO_DATE([Date] || ' ' || [Time_In], 'YYYYMMDD HH24MI')
       
    FROM report
    just to see if that does what you want.

    -PatP

  7. #7
    Join Date
    Mar 2004
    Posts
    11
    Pat you didn't miss the boat I am just not very good at explaining myself. I am using an Oracle database but the tables are linked to access in this case. So all of my SQL code is taking place in an access query. Hope that helps. Thanks again.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So, did the query I provided work (meaning you are using pass-through queries), or did it complain loudly (meaning that you are letting Access process the queries for you)? If you are using Access, try:
    PHP Code:
    SELECT report.Numberreport.Date
    ,  DateValue(Left([Date],4) & "-" Mid([Date],5,2
    &  
    "-" Right([Date],2)) AS Expr1
    ,  report.Time_InTimeValue(Left([Time_in],2) & ":" 
    &  Right([Time_In],2)) AS Expr2
       FROM report

    -PatP

  9. #9
    Join Date
    Mar 2004
    Posts
    11
    Thanks a ton that worked great.
    Brant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •