Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dates In SQL

Posted on 2001-07-02
10
Medium Priority
?
194 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 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

810 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