Text to Time

brants · #1 (**permalink**) 03-25-04, 17:04

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

andrewst · #2 (**permalink**) 03-26-04, 06:18

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.

Pat Phelan · #3 (**permalink**) 03-26-04, 09:58

In SQL Server you could use:

PHP Code:


			
SELECT unit, CAST (Left(a.t, Len(a.t) - 2) + ':' + Right(a.t, 2) 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 a

-PatP

brants · #4 (**permalink**) 03-26-04, 17:27

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.

brants · #5 (**permalink**) 03-26-04, 17:29

The actual code is

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

Pat Phelan · #6 (**permalink**) 03-26-04, 17:59

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

brants · #7 (**permalink**) 03-26-04, 18:12

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.

Pat Phelan · #8 (**permalink**) 03-26-04, 18:53

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.Number, report.Date

,  DateValue(Left([Date],4) & "-" & Mid([Date],5,2) 

&  "-" & Right([Date],2)) AS Expr1

,  report.Time_In, TimeValue(Left([Time_in],2) & ":" 

&  Right([Time_In],2)) AS Expr2

   FROM report;

-PatP

brants · #9 (**permalink**) 03-29-04, 11:15

Thanks a ton that worked great.
Brant

Internet Services

Web Development

Digital Marketing

Consumer Tech