Solved

SQL statement with date

Posted on 2011-02-15
7
846 Views
Last Modified: 2012-05-11
Here is a portion of a Select SQL statement which works.
to_char(AllASPPgmsFinWPmt_tbl.wq_followup,'MM/DD/YYYY') ,

This issue is that it converts data to a string.  I took off the to_char portion which leaves:
AllASPPgmsFinWPmt_tbl.wq_followup,

This seems to work in Microsoft Query but then when Excel is updating I get an error:
Oracle][ODBC] invalid datetimeformat

Thanks in advance for the help.
0
Comment
Question by:jvera524
7 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 34902832
This issue is that it converts data to a string. --> if you don't want it has char and want it has to_date(..) then try the below :

to_date(to_char(AllASPPgmsFinWPmt_tbl.wq_followup,'MM/DD/YYYY'),'MM/DD/YYYY')

1) what are you trying to do ? are you trying to insert data from excel to oracle database ? please explain.

2) AllASPPgmsFinWPmt_tbl.wq_followup --> i believe the data type of this is DATE ?
0
 

Author Comment

by:jvera524
ID: 34903027
Thanks I will try what you said.

I am querying an oracle database, which selects one the fields this way:.
to_char AllASPPgmsFinWPmt_tbl.wq_followup,'MM/DD/YYYY') ,

This works but the field comes to excel as a string, which is why I took out the to_char leaving:
AllASPPgmsFinWPmt_tbl.wq_followup,

This returns the error
Oracle][ODBC] invalid datetimeformat

Please let me know of any questions. Thanks for the help.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34903587
so what format do you want the data in excel to be ?

also did you try this to see if this works ?

to_date(to_char(AllASPPgmsFinWPmt_tbl.wq_followup,'MM/DD/YYYY'),'MM/DD/YYYY')
0
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.

 
LVL 29

Expert Comment

by:gowflow
ID: 34904395
if ur not getting your data (date) correct in Excel it could be due to following:

1) format of the data sent to excel MM/DD/YYYYY conflict with the setting of your PC (DD/MM/YY or anything like this)

2) Excel is not recognizing that the data received is date format so here you hould convert the data to Date

Unfortunately I do not know the syntax in Oracle but if to_date() converts to date then I beleive nav kum v gave you the answer.

gowflow
0
 
LVL 13

Expert Comment

by:riazpk
ID: 34904431
Before your query, set the default date display format as MM/DD/YYYY so that you don't need to use TO_CHAR later:

ALTER SESSION SET nls_date_format = 'MM/DD/YYYY';

Now you can use AllASPPgmsFinWPmt_tbl.wq_followup instead of TO_CHAR(AllASPPgmsFinWPmt_tbl.wq_followup,.....).
0
 

Author Comment

by:jvera524
ID: 34911310
Hello I thought this would be simple bet I can not figure out how to do so.  

When the following partial select statement:
to_date(to_char(AllASPPgmsFinWPmt_tbl.wq_followup,'MM/DD/YYYY'),'MM/DD/YYYY')
 is returned to Excel the field name is the same as above.

I want to rename it to DOS as part of the select statement.  How do I so?
I thuoght it would be
to_date(to_char(AllASPPgmsFinWPmt_tbl.wq_followup as DOS,'MM/DD/YYYY'),'MM/DD/YYYY')
or
to_date(to_char(AllASPPgmsFinWPmt_tbl.wq_followup,'MM/DD/YYYY'),'MM/DD/YYYY') as DOS
 


0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34912177
In oracle the alias for the 'expression' is after.

Why are you wanting to convert a date, to a char and back to a date?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

860 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