Solved

SQL statement with date

Posted on 2011-02-15
7
845 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

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.

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…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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