Solved

Dates In SQL

Posted on 2001-07-02
10
180 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:DSE
  • 4
  • 2
  • 2
  • +2
10 Comments
 

Accepted Solution

by:
jastroem earned 30 total points
ID: 6244655
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
 
LVL 4

Expert Comment

by:ravi_raj123
ID: 6276892
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
 

Author Comment

by:DSE
ID: 6277105
thanks ravi_raj123...will try that.
0
 
LVL 4

Expert Comment

by:ravi_raj123
ID: 6416830
DSE, did my solution solved your problem???
0
 

Expert Comment

by:mbekkk
ID: 6501587
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Expert Comment

by:mbekkk
ID: 6501663
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
 

Expert Comment

by:amp072397
ID: 6783637
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
 

Expert Comment

by:mbekkk
ID: 6783862
Hi,
You can try:

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

0
 

Author Comment

by:DSE
ID: 6786839
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
 

Expert Comment

by:mbekkk
ID: 6786981
I want the points also.....:P)

mbekkk
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now