I’m using Toad for Oracle 12.6 to import data into a table from an Excel spreadsheet. One of the columns, REPORTING_DATE, is not in the spreadsheet. This is a DATE column in the table. I just want to set it as a fixed date - 01/23/2015 for all imported rows.
I know that I can use an expression, and I see a DATETIMEFROMSTRING function that seems to be what I want, but it apparently takes four parameters, and there is no clue as to what these parameters are or how to use them.
Looks like it’s Value, Format, Date Separator, Time Separator.
So this should work:
DATETIMEFROMSTRING(‘01/23/2015’, ‘mm/dd/yyyy’, ‘/’, ‘:’)
I’ll fix this in the GUI. You’re right. It’s very un-obvious.
I'm having almost the same issue with just getting "12:00:00 AM". My dates won't be hardcoded, but am testing with a hardcoded date until I get the format correct.
To verify that I didn't have some other issue, I used the exact code you suggested:
[DATETIMEFROMSTRING('01/23/2015', 'mm/dd/yyyy', '/', ':')]
This worked, and loaded the Date as expected.
My dates will come in the format of "yyyy-mm-dd". So I put this format in and tried:
[DATETIMEFROMSTRING('2015-02-23', 'yyyy-mm-dd', '-', ':')]
This Did Not work, and is bringing in "12:00:00 AM"
Where are your date values coming from? If they are coming from the Excel file, you shouldn't have to do any trickery with date format. Just let the import wizard map the column and let it do it's thing. If that's not working, please provide more info, like a sample of what's in your excel file, and your Toad version
Toad version is 12.11.0.95
My data is coming in a .CSV file exported from a SQL Server database. They are being imported into Oracle as a DATE Data Type.
I was having trouble with the Expression Builder even working with the Hard Coded Date, which I was testing with to get the format correct.
But, for my actual data, I have one Date coming in as "2021-10-22" (date only). That imports fine, as is, without any need for the Expression Builder.
The format I’m having trouble with is “2021-10-22 08:58:59.000”. I’ve tried the Expression “[SUBSTRING(Field12, 1, 10) ]” which should return the same format as my first Date field that IS working, but it does not. At Preview Results, it shows the first 10 characters, but when executing, is give me “’2021-10-22’ is not a valid date and time”.
Sample Data:
ContactDate has no problems. ContactDateTime is my issue field:
Mbr_Umid9,SDR_Person_ID,Mbr_Pers_Gen_Key,SDR_Opportunity,MemberPlanDesc,MemberSegment,ContactDate,ContactDateTime,KeyCode,CampaignType,VendorKey,CRMKey,JointKey,Disposition,DispositionDescription,CreatedQueue,CaseClass,CaseIntent,CallDescription,Eligible,Activation,UserID,AssociateFName,AssociateLName,AssociateTeam,Contacts,Source,LoadDate,LastUpdated
000005695,1003872203,9198023039906,0,COMM,COMM,2021-10-22,2021-10-22 08:58:59.000,0,"INBOUND ",NULL,629329100,629329100,NULL,NULL,HUMANA PHARMACY CALLS,BILLING ISSUE (CT & TM),ONE TIME CHARGE REQUEST,BILLING ISSUE (CT & TM) - ONE TIME CHARGE REQUEST,0,0,SXS5510,STEPHANIE,SMITH,HUM PHARM HUMANA PHARMACY CALL CENTER,1,CRM,2021-10-26 08:48:59.747,2021-10-26 08:58:37.000
000005695,1003872203,9198023039906,0,COMM,COMM,2021-10-22,2021-10-22 09:02:11.000,0,"INBOUND ",NULL,629327350,629327350,NULL,NULL,HUMANA PHARMACY CALLS,MEMBER PROFILE/ACCOUNT (CT & TM),ADD/UPDATED CREDIT CARD,MEMBER PROFILE/ACCOUNT (CT & TM) - ADD/UPDATED CREDIT CARD,0,0,SXS5510,STEPHANIE,SMITH,HUM PHARM HUMANA PHARMACY CALL CENTER,1,CRM,2021-10-26 08:48:59.747,2021-10-26 08:58:37.000
000006749,1003893886,810004247417,0,COMM,COMM,2021-10-23,2021-10-23 14:49:26.937,0,PaHub #Tag,NULL,1700789048,1700789048-#HCPRtoRxENoOffer,NULL,NULL,PaHub,PaHub #Tag,#HCPRtoRxENoOffer,PaHub #Tag - #HCPRtoRxENoOffer,1,1,GLQ3804,GERALD,QUIBUAL,HPSI HPS HCPR ADMIN,1,EPOST,2021-10-26 08:52:21.167,2021-10-26 08:58:37.000
Can you export from SQL Server without the fractional seconds on your time values?
If so, you can just set up Toad like this and don't worry about trying to set up any expressions.
It is being sent to me like that. Others use that same file, so it isn't just for my processing.
I do have the Dates, Times and Numbers setup as you show. I even used the Substring function to strip off the fraction part.
Hmm, substring seems like a good approach. I'll take a look at that and get back to you.
This is from the Preview Results screen.
Expressions like the below should work, but they don't because of a bug that I just found. I'll log this internally to get it fixed in an upcoming beta. You can get the beta if you update to version 15, then download and install the beta.
[DATETIMEFROMSTRING(SUBSTRING([Field8], 1, 10),'YYYY-MM-DD', '-', ':') ]
[DATETIMEFROMSTRING(SUBSTRING([Field28], 1, 19),'YYYY-MM-DD HH:NN:SS', '-', ':') ]
A possible workaround to import your file. Sorry, I know it's a bit of a hassle. If I think of something better, I'll post again.
)