#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    3
    Rep Power
    0

    Question How to Convert string to date


    I have a date saved in varchar2 colomn.

    how can I convert the vaules into date, so I can use the date to compare data.

    Ex: I have the value '20-03-2007 05:31:29', but this value is saved as varchar2.

    how can I take from this value the date '20-03-2007' as date format?
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    By using the aptly named to_date() function.
    I have the value '20-03-2007 05:31:29', but this value is saved as varchar2
    This is very bad, please review your database design and use proper datatypes.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    3
    Rep Power
    0
    I already used to_date function, but it caused some errors.

    also I used to_char function with to_date.

    I know this is bad, but this data is old, and i'm trying manipulte it.

    many errors like: invalid number, not a valid month,..
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by mis
    I already used to_date function, but it caused some errors.
    Then show us

    - Your SQL statement
    - Your input data
    - The exact error message
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    3
    Rep Power
    0
    this is the query and the error:

    1 Select to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy'),
    2 count(BATCHNAME) Batch_Count, Sum(DOCCOUNT) Doc_Count,
    3 sum(PAGECOUNT) Page_Count
    4 From reports
    5 Where OPERATION = 73
    6 And to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
    7 between '09-04-2007' And '15-04-2007'
    8 Group by to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy'),OPERATION
    9* Order By to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
    SQL> /
    between '09-04-2007' And '15-04-2007'
    *
    ERROR at line 7:
    ORA-01843: not a valid month


    note: OP_DATETIME is the varchar2 field, contains data like '15-03-2007 06:09:58'


    another error when using the following query:

    1 Select to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy'),
    2 count(BATCHNAME) Batch_Count, Sum(DOCCOUNT) Doc_Count,
    3 sum(PAGECOUNT) Page_Count
    4 From reports
    5 Where OPERATION = 73
    6 And to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
    7 between to_date('09-04-2007','dd-mm-yyyy') And to_date('15-04-2007','dd-mm-yyyy')
    8 Group by to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy'),OPERATION
    9* Order By to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
    SQL> /
    And to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
    *
    ERROR at line 6:
    ORA-01861: literal does not match format string
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    between '09-04-2007' And '15-04-2007'
    *
    ERROR at line 7:
    ORA-01843: not a valid month
    You NLS_DATE_FORMAT is not set to MM-DD-YYYY.
    You should always use to_date with a format model as you did in the second query.
    Originally Posted by mis
    note: OP_DATETIME is the varchar2 field, contains data like '15-03-2007 06:09:58'
    obviously it does not, otherwise you wouldn't get the error message literal does not match format string
    to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
    What is your intention withi this? Why do you convert a date column to a date column? If you want to "strip" off the time portion use the trunc() function.

    But most important: why on earth are you storing a date in a VARCHAR" column? You are now paying the price for this mistake.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Baraboo WI, USA
    Posts
    2
    Rep Power
    0

    Possible solution?


    I haven't tested this (I don't have PHP installed on my server), but I think this should work. If not, I think I've commented it well enough for you to figure out what I'm doing.

    Note that this converts to the UNIX time format (an integer), not an instance of the date class. If you've got enough PHP knowledge to understand this code, though, you should be able to fix that problem very easily.

    Enjoy, and I hope it works for you!

    PHP Code:
    function stringToUnixTime($s)
    {
    	$date = strtok($s, " ");
    	$time = strtok(" ");
     
    	// These are the actual codes for the PHP date format
    	$d /* day     */ = strtok($date, "-");
    	$m /* month   */ = strtok("-");
    	$Y /* year    */ = strtok("-");
     
    	$s /* seconds */ = strtok($time, "-");
    	$i /* minutes */ = strtok("-");
    	$h /* hours   */ = strtok("-");
     
    	return mktime($h, $i, $s, $m, $d, $Y);
    }


    EDIT -
    Oh.. am I really that ignorant that this isn't a conversation about PHP? Hehe, I hope I'm wrong about that, but if I'm right, sorry!
    Last edited by Joetheodd; April 15th, 2007 at 03:36 PM. Reason: Hm, I linked to some API's and they were blocked? Oops.

IMN logo majestic logo threadwatch logo seochat tools logo