Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2013
    Posts
    72

    Unanswered: Help with WHERE & Date clause

    Hi, it's me again
    *some might know me lol*

    I am doing another query which works fine
    but it does not accept the YEAR '2013' format

    WHERE YR_VIEWING.DATEVIEWED = '2013';

    Basically, I am making a query to show all data based on the DateViewed 2013. Now the Viewing table has the following data:

    DateViewed DATE,

    Is the normal format of DATE as yyyy-mm-dd or rather dd-mm-yyyy ???
    Do I have to change to : DateViewed TO_DATE('DD-Mon-YYY') in order for the upcoming data to be displayed as 01-May-2013 ??


    By the way, the imported dates into the Viewing table show as in the following format: 12-Jan-13


    I also tried with ='13' but does not seem to work
    Last edited by Bucki; 05-01-13 at 00:49.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The purpose of TO_DATE is to convert string datatype to DATE datatype.
    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 including format.

    '10-11-12'
    Which is correct DATE below for string above?
    Oct. 11 2012
    Nov. 10 2012
    Nov. 12 2010
    Dec. 11 2010
    Oct. 12 2011
    Dec. 10 2011
    I'll give you 6 guesses, since the first 5 will be incorrect.

    when all else fails, Read The Fine Manual
    TO_DATE
    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
    Apr 2013
    Posts
    72
    To ur question: I think 12 DEC 2010
    I also think that DB by default uses this format yyy-mm-dd

    Not sure why tho'!

    Never the less ... what I tried out and worked for me is this:

    WHERE TableName.DateViewed BETWEEN '01-APR-2013' AND '31-DEC-2013';
    This is the only thing that worked because '2013' on its own didnt work at all

    Have I actually done it right, for once ??
    Or is there a better way ?!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Have I actually done it right, for once ??
    NO

    WHERE TableName.DateViewed BETWEEN TO_DATE('01-APR-2013','DD-MON-YYYY')
    AND TO_DATE('31-DEC-2013','DD-MON-YYYY')
    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.

  5. #5
    Join Date
    Apr 2013
    Posts
    72
    I dont get it why tho?

    Besides, I did not set a format at the "DateViewed DATE, "
    cos I am not sure...:/

    U know what I mean, dont u!?
    Example if we set the DD-MON-YYYY format right at the beginning when creating a table.. then whatever date goes in the date fiel, then it takes it as ex. 01-April-2013

    not sure if I am making sense ... been awake past 22 hours doing this for tomorrow

  6. #6
    Join Date
    Apr 2013
    Posts
    72
    This is what I get whith your format:


    ORA-01830: date format picture ends before converting entire input string
    01830. 00000 - "date format picture ends before converting entire input string"
    *Cause:
    *Action:

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DATE datatype has NO FORMAT!

    Code:
    SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MON-DD'), TO_CHAR(SYSDATE,'DD-MM-YYYY') FROM DUAL;
    
    TO_CHAR(SYSDATE,'YYY TO_CHAR(SY
    -------------------- ----------
    2013-APR-30          30-04-2013
    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.

  8. #8
    Join Date
    Apr 2013
    Posts
    72
    GOOSHHHH!!!

    Nowwww even my previous SQL doesnt work
    what on earth is wrong now ....

  9. #9
    Join Date
    Apr 2013
    Posts
    72
    Can I use this:

    DateViewed To_CHAR(DATE, 'DD-MON-YYYY')
    and create a table with that statement!
    Does it make it so the entered data into that field, will be in that format dd-mon-yyyy


    ?!

  10. #10
    Join Date
    Apr 2013
    Posts
    72
    Never mind ...
    cant believe how blind I am *must be cos im too tired*


    Your suggestions with TO_DATE works perfectly
    is just that I missed a Y in one of them.


    Cheers

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> create table fubar (DateViewed To_CHAR(DATE, 'DD-MON-YYYY'));
    create table fubar (DateViewed To_CHAR(DATE, 'DD-MON-YYYY'))
                                          *
    ERROR at line 1:
    ORA-00907: missing right parenthesis
    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.

  12. #12
    Join Date
    Apr 2013
    Posts
    72
    what ru trying to say ??

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As anacedent pointed out, Oracle is pretty finicky compared to other SQL engines about what it considers a date. Dates are also stored in an internal format (a clump of bits), rather than as human readable text and Oracle processes that clump of bits not a string that represents them.

    There are two major choices (with a few million variations) for selecting a date range.

    For efficiency sake, I'd recommend:
    Code:
       WHERE  TO_DATE('2013-01-01', 'YYYY-MM-DD') <= YR_VIEWING.DateViewed
          AND YR_VIEWING.DateViewed < TO_DATE('2014-01-01', 'YYYY-MM-DD')
    If you are a really lazy typist and have the patience to wait through the poor code performance, it is a lot easier to type:
    Code:
       WHERE 2013 = Year(YR_VIEWING.DateViewed)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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