Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2006
    Posts
    3

    Unanswered: date datatype query

    Posted - 03/09/2006 : 14:57:15
    --------------------------------------------------------------------------------

    Hello forum:
    I am selecting a date field from an oracle database, that table field is of datatype timestamp(6). which means date is store as date including miliseconds, example 20-JUN-03 04.55.14.000000 PM.

    I need to cast this field to date, which i did as:
    cast(event_date as date) and got a return 06/20/03 4:55 pm,

    Question:
    I program need to get this date as 06/20/03 no time included.
    I can't find a way to remove the time from date.
    i use trunc function but did not work

    this is my current code:

    'In database EVENT_DATE IS OF THE TYPE TIMESTAMP(6)

    Set recordSet = dbConn.Execute("SELECT EVENT_NAME,CATEGORY,cast(EVENT_DATE AS DATE) FROM table_name WHERE cast(EVENT_DATE AS DATE) = '" & mDate & "'")

    mDate has to be: MM/DD/YYYY no time

    otherwise application won't work.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try:
    Code:
    ...
    WHERE TRUNC(EVENT_DATE) = TO_DATE('" & mDate & "','MM/DD/YYYY')")
    Or:
    Code:
    ...
    WHERE TO_CHAR(EVENT_DATE,'MM/DD/YYYY') = '" & mDate & "'")



    Last edited by LKBrwn_DBA; 03-09-06 at 18:19.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2006
    Posts
    3
    Still does not work,

  4. #4
    Join Date
    Mar 2006
    Posts
    53
    I believe you need to put the Trunc or To_Char in place of the first cast. The Select clause defines what data will be returned while the Where clause defines which records the data will come from. You appear to be accessing the correct records so your Where is probably correct (although I'm not familiar with cast-I would have used to_char/to_date in the Where clause too, but if it works then don't change it) and the only thing you need to change is the format of the data being returned.

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    CAST(event_date AS DATE) -- gives a date with time element
    TRUNC(CAST(event_date AS DATE)) -- same date, rounded down

    However, presumably EVENT_DATE should fall somewhere between " & mDate & " (I'm not familiar with that syntax - hoping it's a bind variable ) and " & mDate & " +1 day. Perhaps there is a way to construct the date range in this way and so not have to convert EVENT_DATE.
    Last edited by WilliamR; 03-09-06 at 20:27.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    However, presumably EVENT_DATE should fall somewhere between " & mDate & " (I'm not familiar with that syntax - hoping it's a bind variable ) and " & mDate & " +1 day. Perhaps there is a way to construct the date range in this way and so not have to convert EVENT_DATE.
    Well if I'm right mDate is a string and "&" means string concatenation, so I would risk saying : warning, no bind variable here .

    If mDate is a string indeed, then CAST won't work because it casts as a DATE which is compared to a string : warning, implicit convertion ! So follow one of LKBrwn_DBA's propositions for the WHERE clause.

    Concerning the field you are selecting (SELECT clause), just as calvin-c said, if you want a string in a special format, then CONVERT it EXPLICITLY to that format with TO_CHAR(EVENT_DATE,'MM/DD/YYYY'). Dates or timestamps are NOT strings for Oracle (see this thread for more info on DATE) so if you want a string representation of a date you have to tell Oracle in which format you want it. If you don't, it implicitly converts the date to the default session's string date format (what you are currently experiencing with "SELECT cast(EVENT_DATE AS DATE)") , which is very dangerous and definitely to be avoided.

    HTH & regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You need to get rid of that piece of code and start using bind variables instead of hard-coding your variables into the SQL itself. You're using VB, read about the Command and Parameter object, they are usefull to bind variables and problems like this should dissapear once you do that. You're retrieving apples and comparing them to oranges, oracle's trying to cast your hard-code value into the column's datatype with an implicit conversion and fails to do so (probably with ora-01843). You need to either, convert them to the same type, or use the same datatype when doing the comparison. My approach would be to use bind variables (properly) and let VB and Oracle deal with the convertion.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Bottom line is to compare date to date or string to string,
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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