convert string into time

[Gaime]

I'm working with somebody else his access database. He has two tables with times showing like this: 23:25. He made it a textfield where only numbers between 00:00 and 23:59 can be entered. Now I need to create a third table which shows the downtimes of a machine. It has to show the timedifferents between the starting time and the ending time. The downtime also has to be shown as xx:xx. But ofcourse everything has to be converted to time instead of text. How do I do this the best way?

Another question: the date is entered as 4414 (today). The first 4 stands voor de 4 in 2004, the next two numbers '41' stands for the week of the year. The last number, here 4, stands for the day of the week. How can I convert this to 7 october 2004.

If anyone knows an answer please guide me ...

Thx
gaime

 

[donutman]

gaime,
This is the sort of trouble you get into when you store numeric data as text. Rather than creating a kludge to allow you to manipulate text data as though it were datetime (numeric), why not "fix" the problem at its source? Store the information properly and display it using an appropriate interface.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jmeckley]

for the time use convert(datatime, '23:25', 108)

I'm not sure about the other "date" issue. what happens if the date january 1, 2012 or decemeber 25, 1980?

I don't see a fixed seperation point to parse the month, day and year from.

Jason Meckley
Database Analyst
WITF

 

[SQLBill]

Your question sounds like you are ONLY using Microsoft Access. If that is true, you need to post in the Access forums. This forum is for Microsoft SQL Server.

All SQL was not created equal.

-SQLBill

Posting advice: FAQ481-4875

SELECT 'forum'
CASE [SQL TYPE]
WHEN 'MySQL' THEN 'forum436'
WHEN 'Access' THEN 'forum700', 'forum701',
'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'ORACLE' THEN 'forum185' 'forum186', 'forum759'
ELSE 'FORUM669'
END

 

[Gaime]

Thx for the info. I'll try it out tomorrow. I will also post my thread in an other forum. As newbie I wasn't aware of the mistake.

to answer to donutman: I also had the same idea, but it is not so simple. If i change the setting text to short time all data is invalid and will be erased. Not the way to go. Also there are different tables, queries and reports who are linked to this database. I would like to avoid all risks.


greetz
gaime

 

[SQLBill]

Gaime,

That's okay. Lot's of posters think this forum is for just SQL.

Access and SQL Server handle dates and times differently. There isn't any short time, long time, etc in SQL Server. SQL Server doesn't have separate date and time fields. We have DATETIME datatype, Access doesn't.

-SQLBill