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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Saturday, September 19, 2020 at 4:01:32 PM UTC-7, Ulrich Möller wrote:

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

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Scott Sabo formulated on Saturday :

On Saturday, September 19, 2020 at 4:01:32 PM UTC-7, Ulrich Möller wrote:

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

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.

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Sunday, September 20, 2020 at 2:45:43 AM UTC-7, Mike P wrote:

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

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?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Sunday, September 20, 2020 at 8:18:10 PM UTC+1, scott...@henryschein.com wrote:

On Sunday, September 20, 2020 at 2:45:43 AM UTC-7, Mike P wrote:

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

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?

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

Mike P.
20/9/20

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/20/2020 3:18 PM, Scott Sabo wrote:

On Sunday, September 20, 2020 at 2:45:43 AM UTC-7, Mike P wrote:

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

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?

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Am 20.09.2020 um 03:18 schrieb Scott Sabo:

On Saturday, September 19, 2020 at 4:01:32 PM UTC-7, Ulrich Möller wrote:

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

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.

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)