DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Converting Charater String into Date range

  1. #1
    Join Date
    Feb 2004
    Posts
    35

    Converting Charater String into Date range

    Hello,
    We have a table with no Date datatype. However, there is a char (30) data type column where date is stored as string in YYYY/MM/DD format. I would like to select the data from this table based on date criteria. Is there a way to convert character string of yyyy/mm/dd format into desired date range (for instance, how to select all rows that are older than 30 days OR the rows between Jan 1st 2003 to July1st 2003)

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    to_date() ?

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    to_date()
    Jeff Hunter

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by davey23uk
    to_date() ?
    arrgh. fastest fingers in the west...
    Jeff Hunter

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    1-0 to the brits

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Since it's in YYYY/MM/DD format,you could say ...
    Code:
    ...
    Where
       my_char_date Between
          Cast(To_Char(begin_date,'YYYY/MM/DD') As Char(30)) And
          Cast(To_Char(end_date,'YYYY/MM/DD') As Char(30))
    ...
    Be able to use indexes that way, and avoid a lot of overhead.

    Do I get any points?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Hey, where's my points?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    point for effort

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    No points for a solution that won't kill performance, like To_Date() would?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    point docked for being smart in class

Posting Permissions

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


Click Here to Expand Forum to Full Width