DSE
asked on
Dates In SQL
Hi,
Using Ultradev I am querying an mdb to retrieve records based on date. I use a clause as below to do this:
SELECT *
FROM USERS
WHERE DOB = #01/01/01#
Everything works OK and the records I expect are dsiplayed. However my problem starts when I try to connect to an Oracle DB using a DNS. Whenever I use the # characters I am told by an error message that I am using 'Invalid Characters'.
I know that the field I am trying to connect to is a Date/Time field and the same query works when implemented through a linked table query in Access...so why doesn't it work in Dreamweaver?
Any suggestions?
Thanks in advance.
Using Ultradev I am querying an mdb to retrieve records based on date. I use a clause as below to do this:
SELECT *
FROM USERS
WHERE DOB = #01/01/01#
Everything works OK and the records I expect are dsiplayed. However my problem starts when I try to connect to an Oracle DB using a DNS. Whenever I use the # characters I am told by an error message that I am using 'Invalid Characters'.
I know that the field I am trying to connect to is a Date/Time field and the same query works when implemented through a linked table query in Access...so why doesn't it work in Dreamweaver?
Any suggestions?
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks ravi_raj123...will try that.
DSE, did my solution solved your problem???
Hi,
Oracle stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds.
The default date format is DD-MON-YY.
You can use date functions as well. It depend on what date you want to return.
E.g.: add_month ('11-Jan-94',6). This function will return '11-Jul-94' (from jan until July).
To_Char function allows you to convert a date from this default format (DD-MON-YY) to what you specified.
To_number or To_Date functions can be used to convert string to either a number or a date.
You might try:
select * from users where dob = to_date('January 01, 2001', 'month dd, yyyy');
hope that works.
mbekkk
Oracle stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds.
The default date format is DD-MON-YY.
You can use date functions as well. It depend on what date you want to return.
E.g.: add_month ('11-Jan-94',6). This function will return '11-Jul-94' (from jan until July).
To_Char function allows you to convert a date from this default format (DD-MON-YY) to what you specified.
To_number or To_Date functions can be used to convert string to either a number or a date.
You might try:
select * from users where dob = to_date('January 01, 2001', 'month dd, yyyy');
hope that works.
mbekkk
Hi,
Oracle stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds.
The default date format is DD-MON-YY.
You can use date functions as well. It depend on what date you want to return.
E.g.: add_month ('11-Jan-94',6). This function will return '11-Jul-94' (from jan until July).
To_Char function allows you to convert a date from this default format (DD-MON-YY) to what you specified.
To_number or To_Date functions can be used to convert string to either a number or a date.
You might try:
select * from users where dob = to_date('January 01, 2001', 'month dd, yyyy');
hope that works.
mbekkk
Oracle stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds.
The default date format is DD-MON-YY.
You can use date functions as well. It depend on what date you want to return.
E.g.: add_month ('11-Jan-94',6). This function will return '11-Jul-94' (from jan until July).
To_Char function allows you to convert a date from this default format (DD-MON-YY) to what you specified.
To_number or To_Date functions can be used to convert string to either a number or a date.
You might try:
select * from users where dob = to_date('January 01, 2001', 'month dd, yyyy');
hope that works.
mbekkk
DSE:
You have several open questions:
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20245369
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20234443
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20149045
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20144369
To assist you in your cleanup, I'm providing the following guidelines:
1. Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.
2. Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.
3. When grading, be sure to read:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.
4. Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS: https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.
5. If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.
Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!
thanks!
amp
community support moderator
2/4
You have several open questions:
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20245369
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20234443
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20149045
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20144369
To assist you in your cleanup, I'm providing the following guidelines:
1. Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.
2. Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.
3. When grading, be sure to read:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.
4. Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS: https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.
5. If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.
Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!
thanks!
amp
community support moderator
2/4
Hi,
You can try:
SELECT *
FROM USERS
WHERE DOB = '01-JAN-01'
You can try:
SELECT *
FROM USERS
WHERE DOB = '01-JAN-01'
ASKER
sorry for the late response, thanks for the help. I don't know what went wrong with my code but using the # seems to have started working again.
I want the points also.....:P)
mbekkk
mbekkk
The syntax of the query is different in Access and Oracle with when querying on Date field. When you are quering on Date field in MS Access, then you have to use hash(#) and when you are quering in Oracle, u have to use single quotation ('). Samples............
in MS Access...
Select * from Users where joindate=#01/01/01#
in Oracle
Select * from Users where joindate='01/01/01'
Good Luck...:)