Link to home
Create AccountLog in
Avatar of jvera524
jvera524

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jvera524
jvera524

ASKER

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.
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')
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
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,.....).
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
 


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?