INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Problem with format of Date when extracting.

Problem with format of Date when extracting.

(OP)
Hi.

I am brand spanking new at Mimer, basically been battling my way through the reference manual now for about 8 days, and I have hit a brick wall that I cannot seem to get around.

The statement I am trying to execute looks like this:
SELECT
    GBLPER_PERSON_NUMBER,
    GBLPER_SYSTEM,
    GBLPER_SURNAME,
    GBLPER_INITIALS,
    COALESCE(GBLPER_FIRST_NAME,' '),
    CAST(EXTRACT(YEAR FROM "GBLPER_DATE_OF_BIRTH")
    AS VARCHAR (4)) ||
    CAST(EXTRACT(MONTH FROM "GBLPER_DATE_OF_BIRTH")
    AS VARCHAR (2)) ||
    CAST(EXTRACT(DAY FROM "GBLPER_DATE_OF_BIRTH")
    AS VARCHAR (2)),
    COALESCE(GBLPER_GENDER,' '),
    COALESCE(GBLPER_MARITAL_STATUS,' '),
    COALESCE(GBLPER_ID_TYPE,' '),
    COALESCE(GBLPER_ID_NUMBER,' '),
    COALESCE(GBLPER_MAIDEN_NAME,' '),
    COALESCE(GBLPER_TITLE,' '),
    COALESCE(GBLPER_PREVIOUS_SURNAME,' '),
    COALESCE(GBLPER_PREVIOUS_INITIALS,' ')
INTO :SQLGBLPER-PERSON-NUMBER,
     :SQL-GBLPER-SYSTEM,
     :SQL-GBLPER-SURNAME,
     :SQL-GBLPER-INITIALS,
     :SQL-GBLPER-FIRST-NAME,
     :SQL-GBLPER-DATE-OF-BIRTH,
     :SQL-GBLPER-GENDER,
     :SQL-GBLPER-MARITAL-STATUS,
     :SQL-GBLPER-ID-TYPE,
     :SQL-GBLPER-ID-NO,
     :SQL-GBLPER-MAIDEN-NAME,
     :SQL-GBLPER-TITLE,
     :SQL-GBLPER-PREV-SURNAME,
     :SQL-GBLPER-PREV-INITIALS
FROM SYSADM.GBLPER_PERSONS
WHERE GBLPER_ID_NUMBER = :SQL-IN-PER-ID-NO

My problem is that the date is returned as 199015 if the value is '19900105'. I suspect that because I am CASTing the values as VARCHAR that the statement is suppressing leading zeroes and the left justifying the field.

Could anybody please help a lost soul out?

Thanks.
Hayden.

"Of all the things I've lost I miss my mind the most."

RE: Problem with format of Date when extracting.

Hayden,

Yes, you're right, suppressing leading zeroes is the problem. One possible solution is to avoid numeric data types.

Try replacing with the following:

CODE

CAST(SUBSTRING(REPLACE(CAST("GBLPER_DATE_OF_BIRTH" AS VARCHAR(16)), '-', '') FROM 6 FOR 8) AS varchar(8))


Hope that helps,
Jarl

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close