Query using LEN with LEFT

Scott Sabo

unread,

Sep 19, 2020, 4:42:42 PM9/19/20

to

I have a field for time, 24 hour format as hhmmss, that may have 5 or 6 characters based on the hour of the day. I need the hour value only in a new column. I am trying to use an IF statement in a new column to say that if LEN([CTIME]=5, then LEFT([CTIME],1) elseif LEN([CTIME]=6, then LEFT([CTIME],2). Having trouble writing the statement, any help would be appreciated

Ulrich Möller

unread,

Sep 19, 2020, 7:01:32 PM9/19/20

to

Hi,

Am 19.09.2020 um 22:42 schrieb Scott Sabo:
> I have a field for time, 24 hour format as hhmmss, that may have 5 or 6 characters based on the hour of the day. I need the hour value only in a new column. I am trying to use an IF statement in a new column to say that if LEN([CTIME]=5, then LEFT([CTIME],1) elseif LEN([CTIME]=6, then LEFT([CTIME],2). Having trouble writing the statement, any help would be appreciated

The format of a field is usually uninteresting, because it only concerns
the representation. If the field is of type Date, the Hour(myTime)
function can be used to determine the hour.

Ulrich

Scott Sabo

unread,

Sep 19, 2020, 9:18:06 PM9/19/20

to

Hello, this does not work as it is a numeric string, either 65215 which would mean 6:52:15 AM or 185215 which would mean 6:52:15 PM. Since it is just a 5 or 6 digit numeric string I am working with, I an just trying to grab the first character (if 5 digit) or first two if a 6 digit string.

Ron Weiner

unread,

Sep 19, 2020, 9:38:20 PM9/19/20

to

Scott Sabo formulated on Saturday :

Try:
SELECT IIf(Len([time])=6,Left([time],2),Left([time],1)) AS Hour
FROM A_Table

Rdub

--
This email has been checked for viruses by AVG.
https://www.avg.com

Mike P

unread,

Sep 20, 2020, 5:45:43 AM9/20/20

to

Hi Scott,
You could use Left$(CTIME,Len(CTIME)\3).
The backward slash has to be used as the divide operator to give an integer result; 6\3=2, 5\3=1.

Mike P.
20/9/20

PandaData

unread,

Sep 20, 2020, 8:36:20 AM9/20/20

to

On Saturday, September 19, 2020 at 4:42:42 PM UTC-4, scott... wrote:
> I have a field for time, 24 hour format as hhmmss, that may have 5 or 6 characters based on the hour of the day. I need the hour value only in a new column. I am trying to use an IF statement in a new column to say that if LEN([CTIME]=5, then LEFT([CTIME],1) elseif LEN([CTIME]=6, then LEFT([CTIME],2). Having trouble writing the statement, any help would be appreciated

= LEFT(CTIME,LEN(CTIME)-4)

Scott Sabo

unread,

Sep 20, 2020, 3:18:10 PM9/20/20

to

Mike, this worked great! I did find a few instances though in my table where I have a 4 digit number, just minutes and seconds because the record created between midnight and 1 AM. Any idea how to account for these in the code above and return an hour value of zero?

Mike P

unread,

Sep 20, 2020, 5:19:07 PM9/20/20

to

Try
Left$(Right$("00" & CTIME,6),2)

Mike P.
20/9/20

Neil

unread,

Sep 21, 2020, 6:37:42 AM9/21/20

to

How many digits does your routine generate if the record is created at
one second after midnight? Three?

If it is at all practical to change the routine generating the number so
that it always returns a 6-digit value, it may not be too cumbersome to
search for the oddball values and update them.

--
best regards,

Neil

Hagen Weidlich

unread,

Dec 30, 2020, 8:22:58 AM12/30/20

to

I'm with Ulrich here and strongly suggest to change the time to string
conversion. If that really istn't an option then a workable solution
would be:
LEFT$(FORMAT$(CLNG(TimeStr),"000000")),2)
It is doing the whole conversion backwards to get at the hour in that
string. And I bet - haven't tested it though - it is slow as molasses.
So don't use it queries on large datasets.
But it does cover your oddball values and it works.

Hagen