?
Solved

SQL statement with date

Posted on 2011-02-15
7
Medium Priority
?
851 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

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 77

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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

764 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