Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2013
    Posts
    23

    Unanswered: SQL select specific dates

    Hi I am trying to create a query to select a specific date from a date and time column. I have tried this

    SELECT *
    FROM VT_HSBC_IRIS
    WHERE date_trans LIKE '04/12/2013%';

    but all the records are not returned for some reason, the column is formatted as follows '04/12/2013 08:53'

    Thanks in adance!

  2. #2
    Join Date
    Dec 2013
    Posts
    16

    Cool Dates

    Hi

    the hardest part is specific dates.

    Firstly remember Access dates are mostly done in US format.

    What i would do is

    Select*
    FROM (table name)
    WHERE (datefieldname) = e.g 2012

    this should work and then you can add months and days. Any more issues just reply.

    Thanks

  3. #3
    Join Date
    Dec 2007
    Posts
    253
    [QUOTE=angela_g1;6608411]Hi I am trying to create a query to select a specific date from a date and time column[/quote
    Please confirm the datatype of this column. Is it a date or a varchar2 (or something else)

    I have tried this

    SELECT *
    FROM VT_HSBC_IRIS
    WHERE date_trans LIKE '04/12/2013%';
    If it is a date column, then all you need is
    WHERE TRUNC(date_trans) = TO_DATE('04/12/2013','dd/mm/yyyy')
    (assuming that the date that you supplied there is in dd/mm/yyyy format)

    but all the records are not returned for some reason,
    This provides no useful information to anyone trying to help you.
    the column is formatted as follows '04/12/2013 08:53'
    Not if it's of date datatype it's not. Again, please confirm the datatype of the column (it's important here)

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by NewBie13 View Post
    the hardest part is specific dates.
    Not sure what you mean here, but dates are dates, nothing particularly hard about them.

    Firstly remember Access dates are mostly done in US format.
    1. Why are you talking about Access (I'm assuming that you're talking about MS Access? This is an Oracle forum.
    2. In Oracle, dates are not stored in any specific 'format' they are stored in an internal date structure.
    Select*
    FROM (table name)
    WHERE (datefieldname) = e.g 2012
    This would give a syntax error (in Oracle and in Access)

  5. #5
    Join Date
    Sep 2013
    Posts
    23
    Hi

    Newbie13 appreciate you trying to help but this wouldn't work for me unfortunately

    pablolee sorry i should have specified that is a VARCHAR2 datatype column

    you are right i realise that was of no use that all records were not returned...to be more specific only records where date_trans is before 10:00 are returned for example '04/12/2013 08:43' but not '04/12/2013 15:01'

    fairly new to all this I appreciate your patience and help

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by angela_g1 View Post

    pablolee sorry i should have specified that is a VARCHAR2 datatype column
    That is your problem right there. I appreciate that it is likely outwith your control, but if you have any pull, get that situation resolved or be prepared for a WORLD of pain in the db in the future.

    you are right i realise that was of no use that all records were not returned...to be more specific only records where date_trans is before 10:00 are returned for example '04/12/2013 08:43' but not '04/12/2013 15:01'
    It makes no sense that that is the case, the syntax that you posted would return anything that began with the characters '04/12/2013' . Can you post a copy paste of your sql session demonstrating the issue?
    I would suggest that you look to do something like:
    where trunc(to_date(yourcolumn,'dd/mm/yyyy hh:mi:ss')) = to_date('04/12/2013','dd/mm/yyyy/)

  7. #7
    Join Date
    Sep 2013
    Posts
    23
    I have attached a screen shot of my sql developer screen and the results.

    I can request the change to the datatype of the column but it won't be done straight away :/ so for now I will have to work around it.

    Makes no sense to me either but this is what I can see has been returned in the results
    Attached Files Attached Files

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by angela_g1 View Post
    I have attached a screen shot of my sql developer screen and the results.
    cheers

    I can request the change to the datatype of the column but it won't be done straight away
    That is REALLY good news. I'd get the ball rolling on that as soon as you possibly can. It is truly, one of the very worst design decisions in db design
    :/ so for now I will have to work around it.
    I feel your pain.

    Did you try the option in my last post?

  9. #9
    Join Date
    Sep 2013
    Posts
    23
    Yes, I don't know where i am going wrong here but based on what you said i tried this

    select *
    from VT_HSBC_IRIS
    where trunc(to_date(date_trans,'dd/mm/yyyy hh:mi:ss')) = to_date('04/12/2013','dd/mm/yyyy/)

    but it came back with an error message ORA-01756: quoted string not properly terminated
    01756. 00000 - "quoted string not properly terminated"
    *Cause:
    *Action:
    Error at Line: 4 Column: 78

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >where trunc(to_date(date_trans,'dd/mm/yyyy hh:mi:ss')) = to_date('04/12/2013','dd/mm/yyyy/)

    not as above but as below

    where trunc(to_date(date_trans,'dd/mm/yyyy hh:mi:ss')) = to_date('04/12/2013','dd/mm/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.

  11. #11
    Join Date
    Sep 2013
    Posts
    23
    oops silly me :/

    i tried this and got the following error message

    select *
    from VT_HSBC_IRIS
    where trunc(to_date(date_trans,'dd/mm/yyyy hh:mi:ss')) = to_date('04/12/2013','dd/mm/yyyy');

    ORA-01849: hour must be between 1 and 12
    01849. 00000 - "hour must be between 1 and 12"
    *Cause:
    *Action:

  12. #12
    Join Date
    Dec 2007
    Posts
    253
    try hh24 instead of just hh

Posting Permissions

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