?
Solved

Dates In SQL

Posted on 2001-07-02
10
Medium Priority
?
189 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 

Accepted Solution

by:
jastroem earned 120 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
New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

 
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
 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

777 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