[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 853
  • Last Modified:

SQL statement with date

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
jvera524
Asked:
jvera524
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
jvera524Author Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
gowflowCommented:
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
 
riazpkCommented:
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
 
jvera524Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now