Re: Sql mdb

On Wed, 13 Feb 2008 06:32:06 -0000, limelect <limelect@xxxxxxxxxxxx> wrote:

As for date (my last quest) is the date field (time) a string??
Becouse i used it as a date format
i wander if...
Do you know of a function to convert date/time to mdb format?


SQL := 'Select * from table where startdate = #11-09-2008#';

If you use that date format you will come to grief when you run your program on a PC in a different country (did you mean 11'th September or 9'th November?) - or on a PC with different date/time separators or formats.

Better to use the ISO formats "YYYY-MM-DD" which are unambiguously understood by both SQLserver and MSAccess (and other DBs?)

SQLServer: 'yyyy-mm-dd hh:nn:ss'
MS Access: #yyyy-mm-dd hh:nn:ss#

(Why, oh why, oh why, do these two MS databases *still* have different date formats?)


Cobbled together from various units just to give you the general idea - but I've not even tried to compile it!

const cIsoString_DateSeparator = '-' ;
const cIsoString_ClockSeparator = ':' ;


function TimeToIsoString ( const aTime : tTimeX ) : tIsoString ;
begin
result := FormatDateTime ( aTime, 'yyyy-mm-dd hh:nn:ss' ) ;

// Because the DateSeparator is "/", our dashes "-" will be copied to the output
// but ":" denotes the "TimeSeparator", so FormatDateTime will replace it with
// this PC's "TimeSeparator" - so we need to check if it has been altered from ":"
if ( TimeSeparator <> cIsoString_ClockSeparator ) then begin
result := AnsiReplaceText ( result, TimeSeparator, cIsoString_ClockSeparator ) ;
end ;
end ; // TimeToIsoString


function TimeToDatabaseString

( const aTime : tTimeX ;
const aDatabaseEngine : tDatabaseEngine )
: tStringX ;
var
DateDelimiter : tCharX ;
begin
DateDelimiter := SqlDateDelimiter [aDatabaseEngine] ; // Access "#", SQLServer "'"

result := DateDelimiter + TimeToIsoString (aTime) + DateDelimiter ;

end ; // TimeToDatabaseString


tDatabaseEngine = "MSJet", "MSSqlServer"
tIsoString = tStringX = "string"
tTimeX = "tDateTime" ;

--
Paul Scott
Information Management Systems
Macclesfield, UK.
.

Relevant Pages

  • Re: Time Format in Excel
    ... Access date/time fields store points in time encoded as real numbers. ... Duration = EndTime - SetupTime ... depending on the date/time formats you're using!). ... I want to Sum the duration column into a total number of hours. ...
    (microsoft.public.access.externaldata)
  • Re: Help, I cant find the right code
    ... look at the formats for the date/time it shows the general date option which ... Note that you are NOT limited to the named formats. ... John W. Vinson [MVP] ...
    (microsoft.public.access.formscoding)
  • Re: SQL Statement BETWEEN 2 Dates - Advice Please.
    ... Stop using ambiguous date formats! ... (Reverse address to reply.) ... > The access MDB has the 'IDate' formated as Date/Time. ...
    (microsoft.public.inetserver.asp.db)