Time format 1

[surfbum3000]

I have a table with 2 fields representing time, data type text. Sometimes data in the StartTime column is 5 digits with other entries 6 digits. I would like to update the fields to reflect a standard time format HH:MM

StartTime EndTime
80000 81500
100000 110000

Tried UPDATE tblReservedTimeNWCSC SET StartTime = Left([StartTime],4), EndTime = Left([EndTime],4);

But it only works when there are 6 digits.

 

[SkipVought]

Hi,

TIME is part of DATE.

DATE/TIME values are just numbers....

like right NOW in Nort Texas, it's...

38643.49451

which can be FORMATTED any number of ways to display, for instance...

10/18/2005 11:52

or

11:52:06

In short, you are working with STRINGS that can NEVER be FORMATTED as you wish.

Why are you screwing around with STRINGS???

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[surfbum3000]

If I had answers I would not come to this forum requsting help.

 

[SkipVought]

What I'm telling you is that your strings are useless as time values.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[surfbum3000]

Thanks. What am I supposed to do now?

 

[PHV]

RealStartTime: TimeSerial(Val(StartTime)\10000,Val(Left(Right(StartTime,4),2)),Val(Right(StartTime,2)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

You need to change the field spec to TIME and convert the INPUT DATA as PHV has specified.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[surfbum3000]

Thanks PHV. The results look like this 8:00:00 AM. I do not need the seconds or AM/PM. I need the format HH:MM in military time.

RealStartTime: TimeSerial(Val([StartTime])\10000,Val(Left(Right([StartTime],4),2)),Val(Right([StartTime],2)))

 

[PHV]

Simply change the display format of the field.
Don't confuse data storage and data presentation.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

I have a table with 2 fields representing time, data type [red]text[/red].

You need to change the field spec to TIME and convert the INPUT DATA as PHV has specified.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[surfbum3000]

I created a query with this update for the time fields which took care of the time format:

Start: Format([RealStartTime],"Short Time")