Search code examples
sqlms-accessucanaccess

Format Time Field for UCanAccess

Good day,

I have two DateTime fields in Access of which I am using for keeping a calendar date and one for keeping a calendar time. Merged or split, I cannot get my INSERT statements to work using UCanAccess (Even though some work using the JET Engine). Please can someone advise how to adjust the statement I'm trying below to insert the row?

INSERT INTO Calendar(AppDate,AppTime,AssociatedPatientID,Type,ProviderID)
   VALUES (#2015-04-16#,18:20:00.0,0,'app',1);

Also, if possible, could you please provide an example on how to insert into a single field? I attempted the following but got a large variety of exceptions:

#2015-04-16 18:20:00.0,0#

Thanks in advance for any help!

Solution

  • As Time is also a Date component, you need to wrap the values in # tags. However you only need HH:NN:SS not the millisecond part. So #18:20:00# is what you use instead of 18:20:00.0

    So your INSERT Statement looks something like,

    INSERT INTO Calendar(AppDate, AppTime, AssociatedPatientID, Type, ProviderID)
             VALUES (#2015-04-16#, #18:20:00#, 0, 'app', 1);

    Glad to Help ! :)