Date problems

[savok]

Ok the format of the date field in oracle is DD-MMM-YY i think, now i tried using a masked edit box and set the Mask to ##/##/#### and format to DD-MMM-YY

all i want to do is select fields from table where date > masked box date

now its not working and i have no clue why.

Dim searchDate as string
SearchDate = Format(txtDate.Text, "dd-mmm-yy&quot

this gives me SearchDate in the right format but as a string

and when i Select Fields from Table where date > Searchdate it gives an error

if I dim SearchDate as Date and then
SearchDate = Format(txtDate.Text, "dd-mmm-yy&quot
it returns it to me in the DD/MM/YY format
I dont know why

I am totally lost already Any suggestions?

thanks

 

[MichaelRed]

I am not POSITIVE, but ...

Databases DO NOT store data values in any M/d/y format. All 'modern' implementations (that I am aware of) store the date as a NUMBER which is an offset from some "defined" zero date. So if you coerce a date to a number, you might see:

? CDbl(Now()), Now
36963.8089814815 3/13/01 7:24:56 PM

which depicts today (the "NOW" as the 36963th DAY from the beginning of counting days. In an alagous manner, the decimal part is just the "Fraction" of the day 0.808 ... of a Day is teh 19 Hours 24 Minutes ...

So the real question is what is the difference in the 0th day between Oracle and Microsoft. If ther is noe, then the date functions in Microsoft land work as is for Oracle dates, otherwise you need to add the offset between the two 0th dates to the Oracle value to display it in Microsoft.

Another 'interesting' problem arises, as some programs/programmers do not respect the date field attributes (e..g the 0th day) when manipulating the dates so, Some part of an app may store a plian old Microsoft date into an Oracle database (unwisely assuming that NO ONE will ever attempt to read it except himself), and then he leaves and another program/programmer ASSUMES that the date fields in the Orcale db are (Of COURSE!) done in the Oracle Way ... and thus the tower of babbel IS reborn!


Variations on the theme (Tower of Dates BABBLE) abound - approximatly n^n (where n is the number of databases to the power of the number of programmers using each one), (possibly the factorial of the previous result?)

Since I dont really know the 0th date for Oracle, I can only get part of the way into the maze, however you could take a clue from the "Sample" of getting the "number" for Today and get to the 0th day for Microsoft easily:

? Format(1, "Long Date&quot
Sunday, December 31, 1899

Which Clearly shows that Saturday December 30, 1899 IS the oth date for Microsoft.

Now, you could 'more-or-less' follow the schema and get to the 0th date for Oracle and then know the offset. After that, you will only need to figure out what date schema was (an/or maybe still) in use within the database (and fields) and you will 'magically' be in full control of the various variations in date addressing.

Thanks for your patience


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over