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.
DSEAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jastroemConnect With a Mentor Commented:
I think you should check up th syntax - i'm quite sure that Oracle need something else than pound signs # for a date. I'm out of office right now... but I think I can remember that Oracle uses something else for DateFields (?)

perhaps you couold check it up in a SQL Tutorial, or even at the Oracle web site?

/Joergen
0
 
ravi_raj123Commented:
HI,
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...:)
0
 
DSEAuthor Commented:
thanks ravi_raj123...will try that.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
ravi_raj123Commented:
DSE, did my solution solved your problem???
0
 
mbekkkCommented:
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


0
 
mbekkkCommented:
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


0
 
amp072397Commented:
DSE:

You have several open questions:

http://www.experts-exchange.com/jsp/qShow.jsp?qid=20245369
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20234443
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20149045
http://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:
http://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:  http://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
0
 
mbekkkCommented:
Hi,
You can try:

SELECT *
FROM USERS
WHERE DOB = '01-JAN-01'

0
 
DSEAuthor Commented:
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.
0
 
mbekkkCommented:
I want the points also.....:P)

mbekkk
0
All Courses

From novice to tech pro — start learning today.