Maliki Hassani
asked on
Oracle: date coverted to text
Experts,
I am trying to create a primary key for my Oracle table, which will use a couple of fields to be unique. I need to take my report_date field and convert it to a text which mean removing the slashes, spaces and colon. Here is the output of a record in my report_date field:
1/20/2014 8:34:36 AM
need to convert to:
120201483436AM
Thank you
I am trying to create a primary key for my Oracle table, which will use a couple of fields to be unique. I need to take my report_date field and convert it to a text which mean removing the slashes, spaces and colon. Here is the output of a record in my report_date field:
1/20/2014 8:34:36 AM
need to convert to:
120201483436AM
Thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am using Aqua data studio and to delete a record from the edit table mode, it requires a primary key.
Is the table column already a date?
If so, yes. Just create a primary key with two columns: (report_date,report_type)
If so, yes. Just create a primary key with two columns: (report_date,report_type)
Converting dates to text in order to use them as indexes is a terrible idea because it loses value in the process.
For example...
How many values exists between these two dates (end points inclusive?)
to_date('2014-11-17 14:38:00','yyyy-mm-dd hh24:mi:ss')
to_date('2014-11-17 14:39:00','yyyy-mm-dd hh24:mi:ss')
answer: 61 - this is something Oracle understands about date
How many values exist between these two text strings?
'20141117143800'
'20141117143900'
answer: millions, billions, trillions or more, possibly much, much more
why? because in addition to the 61 strings you expect as valid values, because they are text and not actually dates, the possible range of values inludes values like these.
'2014111714380a'
'201411171438z'||chr(13)
What this means for your queries is that index and table scans have wildly different scales in how big Oracle must estimate.
For example...
How many values exists between these two dates (end points inclusive?)
to_date('2014-11-17 14:38:00','yyyy-mm-dd hh24:mi:ss')
to_date('2014-11-17 14:39:00','yyyy-mm-dd hh24:mi:ss')
answer: 61 - this is something Oracle understands about date
How many values exist between these two text strings?
'20141117143800'
'20141117143900'
answer: millions, billions, trillions or more, possibly much, much more
why? because in addition to the 61 strings you expect as valid values, because they are text and not actually dates, the possible range of values inludes values like these.
'2014111714380a'
'201411171438z'||chr(13)
What this means for your queries is that index and table scans have wildly different scales in how big Oracle must estimate.
ASKER
it's actually a string format. I am trying to fix that. The problem is that I am uploading to the table using a macro in excel, which upload as a string.
if you already have text values that look like dates, this is opportunity to improve the data model.
Rather than stripping the values of characters to make some canonical string as a new column, make the new column a date type and convert your text into a date
Rather than stripping the values of characters to make some canonical string as a new column, make the new column a date type and convert your text into a date
Even if you leave it as a varchar2 column, why must you change it to "120201483436AM" to make it part of a primary key?
ASKER
Slightwv, I thought it would look more like a real GUI ID..
Sdstuber, so what you are saying is create new column, run a edit command to update the new column (date format), and then a create a new column ('GUI_ID') and truncate the two columns mentioned above, then set as primary?
Sdstuber, so what you are saying is create new column, run a edit command to update the new column (date format), and then a create a new column ('GUI_ID') and truncate the two columns mentioned above, then set as primary?
>>Slightwv, I thought it would look more like a real GUI ID..
It is an index value not a column so what does it matter what it looks like?
It is an index value not a column so what does it matter what it looks like?
ASKER
great point.. :)
I was not suggesting doing anything to the old columns, I'm assuming legacy code is still using them. If not, then sure you could get rid of them afterward.
But create a new date type column, and populate it with the old text content.
Create the new PK on the date column and the other non-text-date column
But create a new date type column, and populate it with the old text content.
Create the new PK on the date column and the other non-text-date column
ASKER
Experts, I have implemented all the suggestions. My only issue now is my vba script gives an error "Not a valid month" when I try to upload.
What I have done to table is added a new column "Report_Date_Time" as a date format and populated it with the old text as dates.
From the VBA side, I added a new dim as a Date which is set to Now.
For some reason the upload doesn't think it is a date format to be giving that error.
Any ideas?
What I have done to table is added a new column "Report_Date_Time" as a date format and populated it with the old text as dates.
From the VBA side, I added a new dim as a Date which is set to Now.
For some reason the upload doesn't think it is a date format to be giving that error.
Any ideas?
are you using parameters (binding) or are you simply concatenating the value into the sql?
parameters with correct datatypes are better - more efficient and help eliminate MANY conversion issues.
if concatenating then you are getting a text string which may or may not be in the format you need.
if it's not in the correct format, then use TO_DATE() with whatever format applies to the text represetnation of your Date variable, or better yet, use a bind parameters
parameters with correct datatypes are better - more efficient and help eliminate MANY conversion issues.
if concatenating then you are getting a text string which may or may not be in the format you need.
if it's not in the correct format, then use TO_DATE() with whatever format applies to the text represetnation of your Date variable, or better yet, use a bind parameters
ASKER
Okay, that makes sense.. i am concatenating so that is the issue. Are you saying to use to_date on the vba side?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Roger, thanks
ASKER
Thank you for your assistance.
ASKER
So what you are saying is that I can keep it in the same format it is now?