Hi,

I have a hairy question (for me anyway).  Was wondering if the fillowing is
possible in V3.30 DBISAM:

Have a string field which contains the following:

13/07/2008 9:31:00 PM ADMIN

I need to separate the timestamp and username into two separate fields, so:

fld1 is timestamp field = 13/07/2008 9:31:00 PM
fld2 is string = ADMIN

Help appreciated

Regards

Alex

Alex,

<< Have a string field which contains the following:
13/07/2008 9:31:00 PM ADMIN

I need to separate the timestamp and username into two separate fields, so:
fld1 is timestamp field = 13/07/2008 9:31:00 PM
fld2 is string = ADMIN >>


If the split is always in the same character position, try this construct:
SELECT
 SUBSTRING(my_field FROM 1 FOR 21) AS timestamp_field,
 SUBSTRING(my_field FROM 23 FOR 5) AS dept_field
FROM my_table

Regards,
Dan Rootham [Team Elevate]

"Dan Rootham" <roothamd@yahoo.co.uk> wrote in message
news:BFEE3D96-B322-481A-9EA2-D631BE94F6A4@news.elevatesoft.com...
> Alex,
>
> << Have a string field which contains the following:
> 13/07/2008 9:31:00 PM ADMIN
>
> I need to separate the timestamp and username into two separate fields,
> so:
> fld1 is timestamp field = 13/07/2008 9:31:00 PM
> fld2 is string = ADMIN >>
>
>
> If the split is always in the same character position, try this construct:
> SELECT
>  SUBSTRING(my_field FROM 1 FOR 21) AS timestamp_field,
>  SUBSTRING(my_field FROM 23 FOR 5) AS dept_field
> FROM my_table
>


But it probably will not be, since 12:xx:xx is a valid time, and will shift
everything. You need to key on some known constant, such as AM or PM

SELECT
 IF (POS('AM', S) > 0 THEN SUBSTRING(s FROM 1 FOR pos('AM', s) + 2)
   ELSE SUBSTRING(s FROM 1 FOR pos('PM', s) + 2))
   AS timestamp_field,
  IF (POS('AM', S) > 0 THEN SUBSTRING(s FROM pos('AM', s) + 3 for 30)
   ELSE SUBSTRING(s FROM pos('PM', s) + 3 FOR 30))
   AS DEPT_FIELD
FROM t

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:D4E83C5D-33AA-4477-85A1-2F56725B1779@news.elevatesoft.com...
>

Rereading my reply, maybe you should look for ' AM '  and ' PM ' since it is
possible that the name field could contain the AM or PM substring. Adjust
displacements accordingly (+1).

Robert

Thanks for this, with a couple of tweaks that works very well.  Only issue
now is I am trying to convert the value to a timestamp field not a string
field.  So this value as a string - 15/03/2007 7:00:39 PM - into a timestamp
field

As I have at the moment is as follows:

SELECT
 IF (POS('M ' IN  Nurse_Conf_Date) > 0 THEN SUBSTRING(Nurse_Conf_Date FROM
1 FOR pos('M ' IN Nurse_Conf_Date) )
   ELSE NULL)
   AS timestamp_field,
  IF (POS('M ' IN Nurse_Conf_Date) > 0 THEN SUBSTRING(Nurse_Conf_Date FROM
pos('M ' IN Nurse_Conf_Date) + 2 for 30)
   ELSE NULL)
   AS DEPT_FIELD
FROM History

I have tried modifying as follows:

CAST(SUBSTRING(Nurse_Conf_Date FROM 1 FOR pos('M ' IN Nurse_Conf_Date) )  AS
TIMESTAMP)

but all I get back is NULLS.  Maybe the date format isnt in the appropriate
format for translating?  Might be better to create table-driven Delphi code
to convert?

Thanks

Alex


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:D4E83C5D-33AA-4477-85A1-2F56725B1779@news.elevatesoft.com...
>
> "Dan Rootham" <roothamd@yahoo.co.uk> wrote in message
> news:BFEE3D96-B322-481A-9EA2-D631BE94F6A4@news.elevatesoft.com...
>> Alex,
>>
>> << Have a string field which contains the following:
>> 13/07/2008 9:31:00 PM ADMIN
>>
>> I need to separate the timestamp and username into two separate fields,
>> so:
>> fld1 is timestamp field = 13/07/2008 9:31:00 PM
>> fld2 is string = ADMIN >>
>>
>>
>> If the split is always in the same character position, try this
>> construct:
>> SELECT
>>  SUBSTRING(my_field FROM 1 FOR 21) AS timestamp_field,
>>  SUBSTRING(my_field FROM 23 FOR 5) AS dept_field
>> FROM my_table
>>
>
>
> But it probably will not be, since 12:xx:xx is a valid time, and will
> shift everything. You need to key on some known constant, such as AM or PM
>
> SELECT
>  IF (POS('AM', S) > 0 THEN SUBSTRING(s FROM 1 FOR pos('AM', s) + 2)
>    ELSE SUBSTRING(s FROM 1 FOR pos('PM', s) + 2))
>    AS timestamp_field,
>   IF (POS('AM', S) > 0 THEN SUBSTRING(s FROM pos('AM', s) + 3 for 30)
>    ELSE SUBSTRING(s FROM pos('PM', s) + 3 FOR 30))
>    AS DEPT_FIELD
> FROM t
>
> Robert
>
>

"news.elevatesoft.com" <noreply@noreply.com> wrote in message
news:70DA4C26-5C94-44A1-A7DB-9FBA77A02E10@news.elevatesoft.com...
> Thanks for this, with a couple of tweaks that works very well.  Only issue
> now is I am trying to convert the value to a timestamp field not a string
> field.  So this value as a string - 15/03/2007 7:00:39 PM - into a
> timestamp field
>

In order to cast it as datetime, it will have to be in the format

YYYY-MM-DD HH:MM:SS PM

have fun pulling each substring Not hard at all, just that it requires a
bit more SQL code. Year for 4 plus '-' plus month for 2 etc. At least the
time portion is OK you can pull it as a single substring.

Once you have it done then as you indicated wrap the whole mess into a
CAST(IF(POS...etc)) AS TIMESTAMP) MyTimeField it should work OK for you

Robert