Thread: Issue with Date format
07-03-07, 22:10 #1Registered User
- Join Date
- Oct 2004
Unanswered: Issue with Date format
I have a MS SQL temporary database (for data placeholder) which retrieves data from an Oracle database.
Inside one of the view of the oracle there is a particular line which specified date like this:
v.etd BETWEEN (SELECT ADD_MONTHS(SYSDATE,-1) FROM dual) AND SYSDATE
Because of some ad hoc requests, we need to pull out data for only 1st to 20th April so i changed the syntax to:
v.etd BETWEEN '1 april 2007' AND '20 april 2007'
However, it seems like MS SQL does not recognise that date pattern and return no results after executing the package. I know that SQL reads data in mm/dd/yyyy pattern but when i input that, Oracle is complaining that the month is not correct.
Any idea how do i make things work?
07-03-07, 23:11 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
>BETWEEN '1 april 2007' AND '20 april 2007'
You need a refresher course on SQL syntax & Oracle datatypes.
With SQL, the characters between single quote marks are STRING datatype; such as WHERE TOPIC = 'I need more SQL training'
The ONLY proper way to utilize a DATE datatype is to use TO_DATE using an appropriate format mask.You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.