Solved

Oracle: date coverted to text

Posted on 2014-11-17
18
328 Views
Last Modified: 2014-11-18
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
0
Comment
Question by:Maliki Hassani
  • 9
  • 5
  • 4
18 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 40448167
Why convert it to a string?

If it is a string, use TO_DATE to make it a date and use that as part of your primary key.
If it already a date, just use it.

to_date('1/20/2014 8:34:36 AM','MM/DD/YYYY HH:MI:SS AM')
0
 

Author Comment

by:Maliki Hassani
ID: 40448195
So I have a table that already has records.  It doesn't have a primary key. The table can have duplicate report dates but the report_type is being set as, "Daily" or "Weekly".  I am trying to create a primary key using both fields concatenated.

So what you are saying is that I can keep it in the same format it is now?
0
 

Author Comment

by:Maliki Hassani
ID: 40448202
I am using Aqua data studio and to delete a record from the edit table mode, it requires a primary key.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40448211
Is the table column already a date?

If so, yes.  Just create a primary key with two columns:  (report_date,report_type)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40448219
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.
0
 

Author Comment

by:Maliki Hassani
ID: 40448220
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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40448237
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40448245
Even if you leave it as a varchar2 column, why must you change it to "120201483436AM" to make it part of a primary key?
0
 

Author Comment

by:Maliki Hassani
ID: 40448257
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?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40448285
>>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?
0
 

Author Comment

by:Maliki Hassani
ID: 40448308
great point.. :)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40448347
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
0
 

Author Comment

by:Maliki Hassani
ID: 40450251
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?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40450278
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
0
 

Author Comment

by:Maliki Hassani
ID: 40450305
Okay, that makes sense.. i am concatenating so that is the issue. Are you saying to use to_date on the vba side?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 40450324
I'm assuming you have a query in your vba code that you are concatenating.

Either change that query to use bind variables  (better solution)
or
use TO_DATE around the concatenated text so you pass a date value as part of your query, not text
0
 

Author Comment

by:Maliki Hassani
ID: 40450345
Roger, thanks
0
 

Author Closing Comment

by:Maliki Hassani
ID: 40450835
Thank you for your assistance.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now