From Newsgroup: comp.databases.ms-sqlserv

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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.
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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)
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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?
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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
--- Synchronet 3.21d-Linux NewsLink 1.2