Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: validate date format

    Hi all...
    good day

    I am new here and in DB as well, and I would like to ask you this Q:
    How can i write query to validate date of birth format 'YYYYMMDD'

    pls I need your help and appreciate that

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    With Oracle characters between single quote marks are STRINGS!
    'This is a string, 2009-12-31, not a date'
    When a DATE datatype is desired, then use TO_DATE() function.

    DATE datatype have NO inherent "format"; they are stored in internal binary value
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2011
    Posts
    2
    thanks anacedent..

    BTW: this date is stored as VARCHAR and i need to check if each date stored in this format YYYYMMDD (select statement) << i am not sure if this section (oracle) is suitable for my question or not

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >BTW: this date is stored as VARCHAR and i need to check if each date stored in this format YYYYMMDD (select statement)

    This is a bug waiting to bite users!
    I'd write a PL/SQL procedure that would attempt to TO_DATE the string & report via EXCEPTION
    when an invalid string is found
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •