Graham Anderson <[EMAIL PROTECTED]> wrote on 03/23/2005 06:19:34 PM: > stupidly.... > My DateTime field is in this format: > March 23, 2005, 3:49 pm > > If I want to run this sql.... > SELECT TO_DAYS(MAX(DateTime)) - TO_DAYS(MIN(DateTime)) AS record > FROM userLog > > I gather I need some extra function like...... > SELECT TO_DAYS(MAX(Convert _Function(DateTime))) - > TO_DAYS(MIN(Convert _Function(DateTime))) AS record > FROM userLog > > what is that magic function ? >
If the datatype of your date field really is "datetime", "date", "time" or "timestamp" you will not need a conversion function. If you store your date as character data (literally "March 23, 2005, 3:49 pm") then I highly recommend you convert your data so that you store that information in a "datetime" field and not a "char" or "varchar" field. A frequent misconception with new users is to assume that what they see on the screen is how the data is actually stored within the database. The database has several efficient methods of storing several types of data that look quite different than what you see on the screen. Dates, times, dates+times, and floating-point numbers are good examples of this. Have you tried this query just to see if it will work: SELECT TO_DAYS(MAX(DateTime)) - TO_DAYS(MIN(DateTime)) AS record FROM userLog; If it didn't work, did you get an error or were the results something you didn't expect? I am glad to see a newbie that can RTFM, thank you. And, to quote an old saying, the only "stupid" question is the one not asked... Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS Don't forget to CC: the list on all responses.