Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    34

    Unanswered: dateformat question

    Hi All


    I have a varchar2 variable where i get the date which i need
    to store in date column of a table


    the varchar2 value is coming like this

    dt_val := 'Wed Feb 18 07:00:00 IST 2004',


    now i need to convert this into this date format
    'dd/mon/yyyy hh:mi:ss am' and insert into a table

    How to acheive this



    Rgrds

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    TO_DATE(COLUMN_NAME, 'DD/MM/YY HH24:MIS')

    HTH
    Gregg

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Either I or Gregg missed what you want to do.

    Do you want to take a character string and load it into a date column
    OR
    Do you want to take a date and load it into a character column
    OR
    Do you want to take a character string and load it into a character column??


    you could do something close to this if you want to convert a character string into a date:
    PHP Code:
    to_date(dt_val 'Dy Mon DD HH24:MI:SS TZD YYYY'
    someone correct my syntax if it is wrong.
    Not sure about the timezone part.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Feb 2004
    Posts
    11
    Originally posted by The_Duck
    Either I or Gregg missed what you want to do.

    Do you want to take a character string and load it into a date column
    OR
    Do you want to take a date and load it into a character column
    OR
    Do you want to take a character string and load it into a character column??


    you could do something close to this if you want to convert a character string into a date:
    PHP Code:
    to_date(dt_val 'Dy Mon DD HH24:MI:SS TZD YYYY'
    someone correct my syntax if it is wrong.
    Not sure about the timezone part.

    HI

    I think the above to_date converts that char into default date format(dd-mon-yyyy)..If you want to see the date in desired format you need to specify the to_char(date,'format') in the select statement ..or if you want to change the defauly format you need to change NLS_DATE_FORMAT init.ora parameter.

    Thanks.
    learning Oracle

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    he wants to insert a date.
    so you need to convert the string to a date.

    if he is not inserting into a date, then he needs to convert to a date and then to_char to the desired output.

    the poster was not clear enough.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Feb 2004
    Posts
    11
    Originally posted by The_Duck
    he wants to insert a date.
    so you need to convert the string to a date.

    if he is not inserting into a date, then he needs to convert to a date and then to_char to the desired output.

    the poster was not clear enough.

    Hi

    Yes you are correct..what i said is that only..he can't insert a varchar2 into a date column so that needs to be convert into date.but he wants to insert the date column in a particular format.He can't insert date in his desired format until made a change to NLS_DATE_PARAMETER init.ora parameter.The default format is DD-MON-YYYY.With out changing the NLS_DATE_FORMAT he can write select statement using to_char(date,'format').

    I hope now it is clear.

    Thanks
    learning Oracle

  7. #7
    Join Date
    May 2003
    Posts
    34

    date format question

    Hi guys

    Thanks for all your help.

    I could solve it using something like this

    insert into datetest values
    (TO_DATE('Wed Feb 18 07:00:00 IST 2004',
    'Dy Mon dd hh24:mi:ss "IST" yyyy'))


    Rgrds

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296

    Re: date format question

    Originally posted by zulu99
    Hi guys
    Thanks for all your help.
    I could solve it using something like this

    insert into datetest values
    (TO_DATE('Wed Feb 18 07:00:00 IST 2004',
    'Dy Mon dd hh24:mi:ss "IST" yyyy'))
    Rgrds
    hahah!!
    yeah baby!

    I also see you solved the problem with the timezone, nice.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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